partial search performance

To search for partial string we are using WHERE column_name LIKE '%str%'.
But its taking long time to return the results. Is there any way to improve
the performance.
What is the datatype of the column you are searching against. An index would
help majorly but it has to been an allowable type.
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"Ramu" <Ramu@.discussions.microsoft.com> wrote in message
news:2C4BB57B-48AA-41A1-9594-48DCA08AB50A@.microsoft.com...
> To search for partial string we are using WHERE column_name LIKE
> '%str%'.
> But its taking long time to return the results. Is there any way to
> improve
> the performance.
|||datatype of the column is varchar(max). This column contains the description
of the product. Users want to search for a string rather than a word. If
search by a word is the requirement, I would have gone with FTE.
"Warren Brunk" wrote:

> What is the datatype of the column you are searching against. An index would
> help majorly but it has to been an allowable type.
> --
> /*
> Warren Brunk - MCITP,MCTS,MCDBA
> www.techintsolutions.com
> */
> "Ramu" <Ramu@.discussions.microsoft.com> wrote in message
> news:2C4BB57B-48AA-41A1-9594-48DCA08AB50A@.microsoft.com...
>
>
|||using a wildcard at the start of a LIKE expression does not use an INDEX SEEK
but rather an INDEX SCAN so the query will be slow even if you created
indexes on this column
http://myitforum.com/cs2/blogs/jnelson/archive/2007/11/16/108354.aspx
Misbah Arefin
"Ramu" wrote:
[vbcol=seagreen]
> datatype of the column is varchar(max). This column contains the description
> of the product. Users want to search for a string rather than a word. If
> search by a word is the requirement, I would have gone with FTE.
>
> "Warren Brunk" wrote:

Parent-child variable issues that may impact deployment

We are using SSIS for the first time. My team is working on a project that involves putting a date time stamp into a series of tables. These tables are being assembled in a series of child packages being executed by the parent. When the parent runs, we evaluate our timestamp variable as a GETDATE() expression and pass it to the children to be included as a derived column. We don't want the actual runtime of each step to be the timestamp, just the start of the batch (parent).

In order to get the variable to pass over to the child, we needed to set the package location to "file system"instead of "SQL Server". It seems unusual that this would be so. Are we doing something wrong?

What implications does this have for deployment? Will we need to customize the packages for each instance we plan to run this on? Can you have a parent run a child package on a different instance? This would be a performance plus since we have really huge source databases and would like to distribute the processing.

Hmmm, my boss just told me to scratch the whole idea of parent-child and go with a control table to store the variable for all the packages to access. Oh well, I'm still interested in why this is so cumbersome when really its just passing a parameter from one procedure to another.

Oh, and I think you could use a spellchecker on this message box. At least I could use one.

In the child package, create a variable to hold the parent variable. Same name is fine. Then in the control flow, right click on the background and select "package configurations".

Enable package configurations. Then add a new one. Change the configuration type to "parent package variable." Then, in the specify configuration settings entry, enter the name of the variable in the parent package. Next, on the following screen, select the "value" property of the variable created in your child package. (Expand the child variable until you can select the value property.) Click next. Give the configuration a name and hit finish. Done.

Phil|||

Phil,

Thanks, but we already have that functionality. The problem is that we don't like it. We want something more robust, sort of like passing a parameter from one function to another. And we can't even pass a value parameter with this thingy. This parent-child functionality is just plain ugly. We are using a control table instead, but we don't like doing that either.

Partitioning Strategery

Hi all,

I've got several large fact tables that I want to partition by time. What's the best way to strategy to manage this? I have a C# app that uses AMO to manage the re-processing currently, but right now I'm fairly naive and just re-process every measure group.

What I'd like is a way to automatically partition by month / day [depending on measure group], and then enhance my re-process app to only process the latest partition.

My current idea is to establish a naming structure for the existing MGs/partitions, [i.e. "Foo 12-2006"], parse the date, adjust the named query, create a new partition if needed, and re-process if appropriate. Though it's quite doable, it seems a bit complex for something that should be a more of a mainstream scenario.

How have you guys implemented this functionality? I saw something about specifying the "slice" dimension on partitions, but I'm not sure if that can be used to help this.

Cheers,

Arjun

The follwoing is my experience for different cases when refreshing data in a partitioned cube.

C1. New data for a new time period

Incrementally processing the time dimension, creating the new partition for the new time period and fully process it.

C2. New data for existing time period

Create meta data in RDBMS such as batch etc, merge the new data into existing partition

C3. Updated or deleted data for existing period

Fully process the partition

In SSAS2005, slice is automatically set for msolap partition, and it is only used for querying, not for processing, so you maybe need not to consider it at all.

|||

CJ - thanks for your insight.

Typically my cases fall under (C1) - new data for a new time period. What's the best way to manage new partition creation? Does SSAS provide any functionality to make this easier, or do I need to build my own infrastructure?

|||

If a partition strategery works for you, it is a good one.

I always build the partitions logic by myself.

Partitioned view broken after moving table to new filegroup

I am using SQL Server 2000, SP3.

I created an updatable partitioned view awhile ago and it has been
running smoothly for some time. The partition is on a DATETIME column
and it is partitioned by month. Each month a stored procedure is
scheduled that creates the new month's table, and alters the view to
include it. Again... working like a charm for quite some time.

This past weekend I moved some of the first tables onto a new file
group. I did this through Enterprise Manager, by going into design
mode for the table, then going into the properties for the table and
changing the file group there as well as in all of the indexes. Now
the partitioned view is no longer updatable. It gives the error
message: "UNION ALL view '<view name>' is not updatable because a
partitioning column was not found."

I have extracted the DDL for all of the partition tables and compared
them and they all look the same. I checked and then double-checked the
CHECK constraints to make sure that they were all valid and they are.
If I remove the tables that I moved to the new file group from the
view, then it is once again updatable, but when I put them back in it
fails again.

Any ideas? If you would like samples of the code then I can send it
along, but it's rather large, so I have not included it here.

Thanks!
Thomas R. HummelThomas R. Hummel (tom_hummel@.hotmail.com) writes:
> I created an updatable partitioned view awhile ago and it has been
> running smoothly for some time. The partition is on a DATETIME column
> and it is partitioned by month. Each month a stored procedure is
> scheduled that creates the new month's table, and alters the view to
> include it. Again... working like a charm for quite some time.
> This past weekend I moved some of the first tables onto a new file
> group. I did this through Enterprise Manager, by going into design
> mode for the table, then going into the properties for the table and
> changing the file group there as well as in all of the indexes. Now
> the partitioned view is no longer updatable. It gives the error
> message: "UNION ALL view '<view name>' is not updatable because a
> partitioning column was not found."
> I have extracted the DDL for all of the partition tables and compared
> them and they all look the same. I checked and then double-checked the
> CHECK constraints to make sure that they were all valid and they are.
> If I remove the tables that I moved to the new file group from the
> view, then it is once again updatable, but when I put them back in it
> fails again.
> Any ideas? If you would like samples of the code then I can send it
> along, but it's rather large, so I have not included it here.

Without sample code, only wild guesses are possible.

But one possbility is that as part of the process the CHECK constraints
were disabled and then enabled without WITH CHECK. Then SQL Server does not
trust those constraints.

Try do:

ALTER TABLE tbl NOCHECK CONSTRAINT your_constraint
ALTER TABLE tbl WITH CHECK CHECK CONSTRAINT your_constraint

Moral: don't use Enterprise Manager, if you don't know what it does
behind your back.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland! That was exactly the problem.

As a related followup... if I know that the data in the table
satisfies the constraint, can I make a change to sysobjects to set the
status for the check constraint object to "2"? I understand all of the
usual warnings about making changes to system tables. Just wondering
if it tracks the check constraint information anywhere else.

Since the column was part of the clustered primary key doing the check
on the constraint took less than 1 minute for a table of over 20M
rows, but it might be a useful trick if the check would take longer
than that.

Thanks again,
-Tom.

Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns9590ED2AA1AC6Yazorman@.127.0.0.1>...

> But one possbility is that as part of the process the CHECK constraints
> were disabled and then enabled without WITH CHECK. Then SQL Server does not
> trust those constraints.
> Try do:
> ALTER TABLE tbl NOCHECK CONSTRAINT your_constraint
> ALTER TABLE tbl WITH CHECK CHECK CONSTRAINT your_constraint
> Moral: don't use Enterprise Manager, if you don't know what it does
> behind your back.|||Thomas R. Hummel (tom_hummel@.hotmail.com) writes:
> As a related followup... if I know that the data in the table
> satisfies the constraint, can I make a change to sysobjects to set the
> status for the check constraint object to "2"? I understand all of the
> usual warnings about making changes to system tables. Just wondering
> if it tracks the check constraint information anywhere else.

I for one would never try to do such a thing. It might work, but I don't
like unpleasant surprises later on.

> Since the column was part of the clustered primary key doing the check
> on the constraint took less than 1 minute for a table of over 20M
> rows, but it might be a useful trick if the check would take longer
> than that.

The clustered index does not have much to do it, but the non-clustered
indexes on the table were. Since the clustered index columns act as
row locator in the NC indexes, SQL Server could take a shortcut and scan
the smallest NC index, rather than the whole table.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Parameters in Execute SQL Task after SP2

Hi,

we have a Package, that worked fine for a long time. Now, it always fails on all computers with SP2 instaled. The problem apears in execute sql task, which uses ole db connection manager and calls a procedure with some input parameters (from sql server).

Package sends a undesirable data (".") into the procedure from variable, which has NULL or Empty string value. Originaly, data type of the Variable was Object, I tried to use also string, but in both cases the procedure was called with parameter "."

Please, can anybody tell me what's wrong?

Thanks

Janca

Do you have output parameters in your stored procedure?

In SP2 there was a change to the way stored procedures with output parameters are executed. Earlier there was no way to specify the parameter size and hence we were defaulting to the behavior of each provider when output parameters are involved. In SP2, you could specify the parameter size for the output paramters of variable length data types. Even this change should not break existing packages since the default value of the parameter is -1 should provider the pre-SP2 behavior.

If you can share the values of different properties on your execute sql task, we can take a look and see what is wrong with it?

|||I have a similar issue in SP2. I have a variable V1 (string) which I reference in the ParameterMapping of an Exec SQL Task. The Exec Sql Task uses an OLEDB connection manager to SqlServer 2005. In the parameter mapping I specify VARCHAR and parameter size = -1. The Sql Statement executes a stored proc with one INPUT parameter @.P1 VARCHAR(500). In the proc I test the length of @.P1. If I leave the SSIS variable V1 empty, when I exec the proc it says @.P1 is length = 1. I would expect the length to be zero. The value seems to be the ASCII NUL value (0). How can I get it to be an empty string as I would expect it to be? Thanks.

Parameterized queries taking more time

Do parameterized queries take up more CPU resources than if the where clause
values were hard coded ?
Thanks.Chakravarthy,
can you tell us where are you testing this and how?
AMB
"Chakravarthy" wrote:
> Do parameterized queries take up more CPU resources than if the where clause
> values were hard coded ?
> Thanks.
>
>|||I'm sure they take up a very, very, very small amount more than a
hard-coded query. The query PLAN that's used is far and away the most
important thing to consider, though. If the plans are identical, you
will have queries which, under the same conditions, will perform
identically.
Chakravarthy wrote:
> Do parameterized queries take up more CPU resources than if the where clause
> values were hard coded ?
> Thanks.
>

Parameterized queries taking more time

Do parameterized queries take up more CPU resources than if the where clause
values were hard coded ?
Thanks.
Chakravarthy,
can you tell us where are you testing this and how?
AMB
"Chakravarthy" wrote:

> Do parameterized queries take up more CPU resources than if the where clause
> values were hard coded ?
> Thanks.
>
>
|||I'm sure they take up a very, very, very small amount more than a
hard-coded query. The query PLAN that's used is far and away the most
important thing to consider, though. If the plans are identical, you
will have queries which, under the same conditions, will perform
identically.
Chakravarthy wrote:
> Do parameterized queries take up more CPU resources than if the where clause
> values were hard coded ?
> Thanks.
>

Parameterized queries taking more time

Do parameterized queries take up more CPU resources than if the where clause
values were hard coded ?
Thanks.Chakravarthy,
can you tell us where are you testing this and how?
AMB
"Chakravarthy" wrote:

> Do parameterized queries take up more CPU resources than if the where clau
se
> values were hard coded ?
> Thanks.
>
>|||I'm sure they take up a very, very, very small amount more than a
hard-coded query. The query PLAN that's used is far and away the most
important thing to consider, though. If the plans are identical, you
will have queries which, under the same conditions, will perform
identically.
Chakravarthy wrote:
> Do parameterized queries take up more CPU resources than if the where clau
se
> values were hard coded ?
> Thanks.
>

Parameterized connection strings for report parameters

Hi,
I'm posting this again - it is in fact the same issue all the time but
I'm just trying to tighten up the scope of question as I working on
the problem myself. Does anybody have used parameterized connection
strings for stored procedures used to populate report parameter
dropdown? It's just doesn't work! Even if I supply default parameter
values! Any ideas?
TommyIt doesn't work for drop downs, and why it should work? because for drop down
populating if you are passing a parameterized query then no meaning of drop
down instead it will be a main program for working as entry screen ofcourse
unless you have parameter as user id then it is different.
Amarnath, MCTS
"Tomaszek" wrote:
> Hi,
> I'm posting this again - it is in fact the same issue all the time but
> I'm just trying to tighten up the scope of question as I working on
> the problem myself. Does anybody have used parameterized connection
> strings for stored procedures used to populate report parameter
> dropdown? It's just doesn't work! Even if I supply default parameter
> values! Any ideas?
>
> Tommy
>|||Query (stored procedure) is not parameterized. What is parameterized
is connection string in data source used to run this stored procedure.
Report parameters. So why it shouldn't work? Especially that the same
parameterized connection string is working fine for stored procedure
used to fill ontent of the report.
Tommy
On Mar 21, 10:54 am, Amarnath <Amarn...@.discussions.microsoft.com>
wrote:
> It doesn't work for drop downs, and why it should work? because for drop down
> populating if you are passing a parameterized query then no meaning of drop
> down instead it will be a main program for working as entry screen ofcourse
> unless you have parameter as user id then it is different.
> Amarnath, MCTS
>
> "Tomaszek" wrote:
> > Hi,
> > I'm posting this again - it is in fact the same issue all the time but
> > I'm just trying to tighten up the scope of question as I working on
> > the problem myself. Does anybody have used parameterized connection
> > strings for stored procedures used to populate report parameter
> > dropdown? It's just doesn't work! Even if I supply default parameter
> > values! Any ideas?
> > Tommy- Hide quoted text -
> - Show quoted text -|||Sorry, I thought you are parameterizing the stored proc for drop down. can
you let me know what you have given in your parametereized connection
string.. For e.g. It should be something like this.
="data source=" & Parameters!ServerName.Value & ";initial
catalog=AdventureWorks
But some restrictions are there in creating this. I would suggest you go
through this link before writing one. search string like "Data Source
Expressions" in this link.
http://msdn2.microsoft.com/en-us/library/ms156450.aspx
Amarnath, MCTS
"Tomaszek" wrote:
> Query (stored procedure) is not parameterized. What is parameterized
> is connection string in data source used to run this stored procedure.
> Report parameters. So why it shouldn't work? Especially that the same
> parameterized connection string is working fine for stored procedure
> used to fill ontent of the report.
> Tommy
> On Mar 21, 10:54 am, Amarnath <Amarn...@.discussions.microsoft.com>
> wrote:
> > It doesn't work for drop downs, and why it should work? because for drop down
> > populating if you are passing a parameterized query then no meaning of drop
> > down instead it will be a main program for working as entry screen ofcourse
> > unless you have parameter as user id then it is different.
> >
> > Amarnath, MCTS
> >
> >
> >
> > "Tomaszek" wrote:
> > > Hi,
> > > I'm posting this again - it is in fact the same issue all the time but
> > > I'm just trying to tighten up the scope of question as I working on
> > > the problem myself. Does anybody have used parameterized connection
> > > strings for stored procedures used to populate report parameter
> > > dropdown? It's just doesn't work! Even if I supply default parameter
> > > values! Any ideas?
> >
> > > Tommy- Hide quoted text -
> >
> > - Show quoted text -
>
>|||Hi,
I have already visited mentioned link as lika a dozen others.
Connection string looks fairly simple
="Data Source=" & Parameters!SvrName.Value & ";Initial Catalog=" &
Parameters!DBName.Value
And as I write previously it works fine for filling the report
content. Alas when I'm using it for stored procedure that wills
parameter dropdown it fails. It faile even if I supply default values
for parameters.
It looks like parameters are avaliable after hitting 'view report
button' I mean after user will supply it's own report parameters - if
so this is the reason why they are unavaliable for connection string
that is needed actually for displaying parameters. But this are only
my guesses. Hope I described clearly what is happening. If you have
any suggestions please post them.
Thank you in advance.
Tommy.
On Mar 21, 11:55 am, Amarnath <Amarn...@.discussions.microsoft.com>
wrote:
> Sorry, I thought you are parameterizing the stored proc for drop down. can
> you let me know what you have given in your parametereized connection
> string.. For e.g. It should be something like this.
> ="data source=" & Parameters!ServerName.Value & ";initial
> catalog=AdventureWorks
> But some restrictions are there in creating this. I would suggest you go
> through this link before writing one. search string like "Data Source
> Expressions" in this link.
> http://msdn2.microsoft.com/en-us/library/ms156450.aspx
> Amarnath, MCTS
>
> "Tomaszek" wrote:
> > Query (stored procedure) is not parameterized. What is parameterized
> > is connection string in data source used to run this stored procedure.
> > Report parameters. So why it shouldn't work? Especially that the same
> > parameterized connection string is working fine for stored procedure
> > used to fill ontent of the report.
> > Tommy
> > On Mar 21, 10:54 am, Amarnath <Amarn...@.discussions.microsoft.com>
> > wrote:
> > > It doesn't work for drop downs, and why it should work? because for drop down
> > > populating if you are passing a parameterized query then no meaning of drop
> > > down instead it will be a main program for working as entry screen ofcourse
> > > unless you have parameter as user id then it is different.
> > > Amarnath, MCTS
> > > "Tomaszek" wrote:
> > > > Hi,
> > > > I'm posting this again - it is in fact the same issue all the time but
> > > > I'm just trying to tighten up the scope of question as I working on
> > > > the problem myself. Does anybody have used parameterized connection
> > > > strings for stored procedures used to populate report parameter
> > > > dropdown? It's just doesn't work! Even if I supply default parameter
> > > > values! Any ideas?
> > > > Tommy- Hide quoted text -
> > > - Show quoted text -- Hide quoted text -
> - Show quoted text -