Access DataSet in Code Window

I would like to access datasets in a report from code in the code window. I
found how to access parameters from the code window but I do not see a way to
iterate through a dataset.
Thanks
Matt AdamsWhat are you trying to accomplish. Maybe there is a better (more RS way) of
accomplishing it.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:F3637F35-4FC1-45C6-82CA-67B1657A6C24@.microsoft.com...
>I would like to access datasets in a report from code in the code window.
>I
> found how to access parameters from the code window but I do not see a way
> to
> iterate through a dataset.
> Thanks
> Matt Adams|||Hi Bruce,
Thanks for your prompt reply. Here is what I am trying to accomplish.
I have textboxes on the report that list the parameters that were selected
when the report was ran (because our users just love to print for some
reason). The parameters are multi-value parameters and those values are
pulled from lookup tables into a RS dataset. If the "Select All" is
selected, then I display "All" in that parameter's textbox, otherwise I
display exactly what is selected. I currently have this working in the code
window by defining the full string of current values and comparing this
against a Join statment on the Parameter label. This works fine but if the
list of values changes at the database, I need the report to be able to
handle this in a dynamic fashion.
Thanks for your assistance.
Matt Adams
"Bruce L-C [MVP]" wrote:
> What are you trying to accomplish. Maybe there is a better (more RS way) of
> accomplishing it.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Matt" <Matt@.discussions.microsoft.com> wrote in message
> news:F3637F35-4FC1-45C6-82CA-67B1657A6C24@.microsoft.com...
> >I would like to access datasets in a report from code in the code window.
> >I
> > found how to access parameters from the code window but I do not see a way
> > to
> > iterate through a dataset.
> >
> > Thanks
> >
> > Matt Adams
>
>|||OK, you don't need to be accessing the data directly. You can do all this
with the parameters object. Just remember before writing code, expressions
are your friend. It is very rare indeed that I write code (I do in a few
cases but not often).
Here is what I do in a textbox. I do one additional thing, if they pick more
than a certain number I just say they picked more than X parameters.
Container is the name of the multi-value parameter in this example:
="Containers= " & iif(Parameters!Container.Count > 8," more than 7
Selected",Join(Parameters!Container.Value, ", "))
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:844BFAFD-2CAB-4FE6-91FE-19B1FE651FB6@.microsoft.com...
> Hi Bruce,
> Thanks for your prompt reply. Here is what I am trying to accomplish.
> I have textboxes on the report that list the parameters that were selected
> when the report was ran (because our users just love to print for some
> reason). The parameters are multi-value parameters and those values are
> pulled from lookup tables into a RS dataset. If the "Select All" is
> selected, then I display "All" in that parameter's textbox, otherwise I
> display exactly what is selected. I currently have this working in the
> code
> window by defining the full string of current values and comparing this
> against a Join statment on the Parameter label. This works fine but if
> the
> list of values changes at the database, I need the report to be able to
> handle this in a dynamic fashion.
> Thanks for your assistance.
> Matt Adams
> "Bruce L-C [MVP]" wrote:
>> What are you trying to accomplish. Maybe there is a better (more RS way)
>> of
>> accomplishing it.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Matt" <Matt@.discussions.microsoft.com> wrote in message
>> news:F3637F35-4FC1-45C6-82CA-67B1657A6C24@.microsoft.com...
>> >I would like to access datasets in a report from code in the code
>> >window.
>> >I
>> > found how to access parameters from the code window but I do not see a
>> > way
>> > to
>> > iterate through a dataset.
>> >
>> > Thanks
>> >
>> > Matt Adams
>>|||That is definately a better solution than what I am doing now with a lot less
coding. It will not be dynamic but it will be less maintenance this way.
Thanks alot.
Just for the sake of having knowledge, do you know of a way to access a RS
Dataset from the code window or by calling a referenced component? Say if I
had a need to iterate through the dataset.
Thanks again.
"Bruce L-C [MVP]" wrote:
> OK, you don't need to be accessing the data directly. You can do all this
> with the parameters object. Just remember before writing code, expressions
> are your friend. It is very rare indeed that I write code (I do in a few
> cases but not often).
> Here is what I do in a textbox. I do one additional thing, if they pick more
> than a certain number I just say they picked more than X parameters.
> Container is the name of the multi-value parameter in this example:
> ="Containers= " & iif(Parameters!Container.Count > 8," more than 7
> Selected",Join(Parameters!Container.Value, ", "))
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Matt" <Matt@.discussions.microsoft.com> wrote in message
> news:844BFAFD-2CAB-4FE6-91FE-19B1FE651FB6@.microsoft.com...
> > Hi Bruce,
> >
> > Thanks for your prompt reply. Here is what I am trying to accomplish.
> >
> > I have textboxes on the report that list the parameters that were selected
> > when the report was ran (because our users just love to print for some
> > reason). The parameters are multi-value parameters and those values are
> > pulled from lookup tables into a RS dataset. If the "Select All" is
> > selected, then I display "All" in that parameter's textbox, otherwise I
> > display exactly what is selected. I currently have this working in the
> > code
> > window by defining the full string of current values and comparing this
> > against a Join statment on the Parameter label. This works fine but if
> > the
> > list of values changes at the database, I need the report to be able to
> > handle this in a dynamic fashion.
> >
> > Thanks for your assistance.
> >
> > Matt Adams
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> What are you trying to accomplish. Maybe there is a better (more RS way)
> >> of
> >> accomplishing it.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Matt" <Matt@.discussions.microsoft.com> wrote in message
> >> news:F3637F35-4FC1-45C6-82CA-67B1657A6C24@.microsoft.com...
> >> >I would like to access datasets in a report from code in the code
> >> >window.
> >> >I
> >> > found how to access parameters from the code window but I do not see a
> >> > way
> >> > to
> >> > iterate through a dataset.
> >> >
> >> > Thanks
> >> >
> >> > Matt Adams
> >>
> >>
> >>
>
>|||You can have a custom assembly but it is not trivial. The biggest issue is
dealing with security for custom assemblies.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:8A423D93-D7E2-4DF9-AE23-78C2E67756A6@.microsoft.com...
> That is definately a better solution than what I am doing now with a lot
> less
> coding. It will not be dynamic but it will be less maintenance this way.
> Thanks alot.
> Just for the sake of having knowledge, do you know of a way to access a RS
> Dataset from the code window or by calling a referenced component? Say if
> I
> had a need to iterate through the dataset.
> Thanks again.
> "Bruce L-C [MVP]" wrote:
>> OK, you don't need to be accessing the data directly. You can do all this
>> with the parameters object. Just remember before writing code,
>> expressions
>> are your friend. It is very rare indeed that I write code (I do in a few
>> cases but not often).
>> Here is what I do in a textbox. I do one additional thing, if they pick
>> more
>> than a certain number I just say they picked more than X parameters.
>> Container is the name of the multi-value parameter in this example:
>> ="Containers= " & iif(Parameters!Container.Count > 8," more than 7
>> Selected",Join(Parameters!Container.Value, ", "))
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Matt" <Matt@.discussions.microsoft.com> wrote in message
>> news:844BFAFD-2CAB-4FE6-91FE-19B1FE651FB6@.microsoft.com...
>> > Hi Bruce,
>> >
>> > Thanks for your prompt reply. Here is what I am trying to accomplish.
>> >
>> > I have textboxes on the report that list the parameters that were
>> > selected
>> > when the report was ran (because our users just love to print for some
>> > reason). The parameters are multi-value parameters and those values
>> > are
>> > pulled from lookup tables into a RS dataset. If the "Select All" is
>> > selected, then I display "All" in that parameter's textbox, otherwise I
>> > display exactly what is selected. I currently have this working in the
>> > code
>> > window by defining the full string of current values and comparing this
>> > against a Join statment on the Parameter label. This works fine but if
>> > the
>> > list of values changes at the database, I need the report to be able to
>> > handle this in a dynamic fashion.
>> >
>> > Thanks for your assistance.
>> >
>> > Matt Adams
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> What are you trying to accomplish. Maybe there is a better (more RS
>> >> way)
>> >> of
>> >> accomplishing it.
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "Matt" <Matt@.discussions.microsoft.com> wrote in message
>> >> news:F3637F35-4FC1-45C6-82CA-67B1657A6C24@.microsoft.com...
>> >> >I would like to access datasets in a report from code in the code
>> >> >window.
>> >> >I
>> >> > found how to access parameters from the code window but I do not see
>> >> > a
>> >> > way
>> >> > to
>> >> > iterate through a dataset.
>> >> >
>> >> > Thanks
>> >> >
>> >> > Matt Adams
>> >>
>> >>
>> >>
>>|||Ok. Makes sense. Thanks.
"Bruce L-C [MVP]" wrote:
> You can have a custom assembly but it is not trivial. The biggest issue is
> dealing with security for custom assemblies.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Matt" <Matt@.discussions.microsoft.com> wrote in message
> news:8A423D93-D7E2-4DF9-AE23-78C2E67756A6@.microsoft.com...
> > That is definately a better solution than what I am doing now with a lot
> > less
> > coding. It will not be dynamic but it will be less maintenance this way.
> > Thanks alot.
> >
> > Just for the sake of having knowledge, do you know of a way to access a RS
> > Dataset from the code window or by calling a referenced component? Say if
> > I
> > had a need to iterate through the dataset.
> >
> > Thanks again.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> OK, you don't need to be accessing the data directly. You can do all this
> >> with the parameters object. Just remember before writing code,
> >> expressions
> >> are your friend. It is very rare indeed that I write code (I do in a few
> >> cases but not often).
> >>
> >> Here is what I do in a textbox. I do one additional thing, if they pick
> >> more
> >> than a certain number I just say they picked more than X parameters.
> >> Container is the name of the multi-value parameter in this example:
> >> ="Containers= " & iif(Parameters!Container.Count > 8," more than 7
> >> Selected",Join(Parameters!Container.Value, ", "))
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >>
> >> "Matt" <Matt@.discussions.microsoft.com> wrote in message
> >> news:844BFAFD-2CAB-4FE6-91FE-19B1FE651FB6@.microsoft.com...
> >> > Hi Bruce,
> >> >
> >> > Thanks for your prompt reply. Here is what I am trying to accomplish.
> >> >
> >> > I have textboxes on the report that list the parameters that were
> >> > selected
> >> > when the report was ran (because our users just love to print for some
> >> > reason). The parameters are multi-value parameters and those values
> >> > are
> >> > pulled from lookup tables into a RS dataset. If the "Select All" is
> >> > selected, then I display "All" in that parameter's textbox, otherwise I
> >> > display exactly what is selected. I currently have this working in the
> >> > code
> >> > window by defining the full string of current values and comparing this
> >> > against a Join statment on the Parameter label. This works fine but if
> >> > the
> >> > list of values changes at the database, I need the report to be able to
> >> > handle this in a dynamic fashion.
> >> >
> >> > Thanks for your assistance.
> >> >
> >> > Matt Adams
> >> >
> >> > "Bruce L-C [MVP]" wrote:
> >> >
> >> >> What are you trying to accomplish. Maybe there is a better (more RS
> >> >> way)
> >> >> of
> >> >> accomplishing it.
> >> >>
> >> >>
> >> >> --
> >> >> Bruce Loehle-Conger
> >> >> MVP SQL Server Reporting Services
> >> >>
> >> >> "Matt" <Matt@.discussions.microsoft.com> wrote in message
> >> >> news:F3637F35-4FC1-45C6-82CA-67B1657A6C24@.microsoft.com...
> >> >> >I would like to access datasets in a report from code in the code
> >> >> >window.
> >> >> >I
> >> >> > found how to access parameters from the code window but I do not see
> >> >> > a
> >> >> > way
> >> >> > to
> >> >> > iterate through a dataset.
> >> >> >
> >> >> > Thanks
> >> >> >
> >> >> > Matt Adams
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>sql

about related parameters

a reporting project with 4 parameters,eg.paraA paraB paraC paraD if the
user selects paraA=B then paraB will appears.can this be achieved?Yes, sort of.
You can't have them hidden, but you can have them dependent on one
another so that B can't be selected until after A is, etc.
You would need seperate data sources set up for stored procedures
similar to the following, the paramiters would need to be set up to
query the data source for values:
(optional, you could manually add these values or allow the user to
type a value)
GetParaAList
(required)
GetParaBList @.ParaA
GetParaCList @.ParaB
GetParaDList @.ParaC
You could then have a final stored proceedure:
GetMyReportValues @.ParaA, @.ParaB, @.ParaC, @.ParaD
Note that in this case the report cannot be run unless A,B,C, and D
have values selected.

About inserting some parameters to a stored procedure.

Hello everyone,

I am having problem with a program that gets some input from a webform and inserts to a stored procedure, I am getting the two error Error messages below, can somebdoy have a look my code below and put me in the right direction. thanks in advance

Errors

'System.Data.SqlClient.SqlCommand' does not contain a definition for 'InsertCommandType'

'System.Data.SqlClient.SqlCommand' does not contain a definition for 'InsertCommand'

protected void Button1_Click(object sender, EventArgs e) {/* These two variables get the values of the textbox (i.e user input) and assign two local * variables, This is also a good strategy against any Sql Injection Attacks. * */string Interview1 = TextBox1.Text;string Interview2 = TextBox2.Text;string Interview3 = TextBox3.Text;string ProdMentioned = TextBox4.Text;string ProdSeen = TextBox5.Text;string Summary = TextBox6.Text;string Compere = TextBox7.Text;string Duration = TextBox8.Text;//Create Sql connection variable that call the connection string SqlConnection SqlConnection =new SqlConnection(GetConnectionString());//Create a sql command to excute SQL statement against SQL server SqlCommand Command =new SqlCommand();// Set the command type as one that calls a Stored Procedure. Command.InsertCommandType = CommandType.StoredProcedure;//Call the stored procedure so we can pass it on the user input to retrieve user details Command.InsertCommand ="Summaries";//open the command connection with the connection string Command.Connection = SqlConnection;// Pass the user input to the Stored Procedure to check if user exists in our system. Command.InsertParameters.Add("interview1", interview1); Command.InsertParameters.Add("interview2", interview2); Command.InsertParameters.Add("interview3", interview3); Command.InsertParameters.Add("ProdMentioned", ProdMentioned); Command.InsertParameters.Add("ProdSeen", ProdSeen); Command.InsertParameters.Add("Compere", Compere); Command.InsertParameters.Add("Duration", Duration);int rowsAffected = 0;try { rowsAffected = Command.Insert(); }catch (Exception ex) { Resonse.Redirect("InsertSuccessfull.aspx"); }// open the connection with the command //Command.Connection.Open(); }private static string GetConnectionString() {return ConfigurationManager.ConnectionStrings["BroadcastTestConnectionString1"].ConnectionString; }}

not sure about your errors, but I think if you fix those, you will soon get one because

http://peterkellner.net/2006/10/20/loginfailswithiisnotcasini/

Command.InsertParameters.Add("interview1", interview1);

should be

Command.InsertParameters.Add("@.interview1", interview1);

About Indexes & Where caluse

Hi,

I am struggling with the sequence of parameters in my where clause.

In my databse table i have index on Broadcast_Date(some table field).The Index also include some other parameters which might become part of where clause.

The first field of the index is broadcast date.

So I want to know whether it is always compoulsory to have broadcast date as my first field of where clause.

Will it not scan the index if my where clause is starting with any other field which is also part of index.?

Will it hamper the query performance any way ?

Any help will really be appreciated.

Thanks

Vyanki

Hello,

the order of conditions in your where clause has no impact on index selection. Write in any way, it doesn't matter.

SQL chooses indexes(execution plan) based on the "logic" of your query and the information(statistics) that it available about the indexes that might be used to answer your query.
First, the SQL query is compiled into a "Query tree". This is a tree of algebraic operations, each of them has its corresponding SQL-counterpart.
Then, the query optimizer makes transformations of the query tree, and obtains new trees. These new trees are semantically equivalent to the initial tree, but they have different costs in terms execution.The cost of execution of each tree obtained is evaluated, based on index statistics. The one with the least cost is chosen as query plan.
Example. The query
select * from Table1 t1 join Table2 t2 on t1.fISN=t2.fISN join Table3 t3 on t2.fCODE=t3.fCODE
can be executed at least in 2 ways: first join Table2 and Table3, then join the resulting temporary table with Table1, or the other way around-join Table1 and Table2, then join the result with Table3. The optimiser considers both opportunities and chooses the "cheaper" one.

P.S. If it is permitted in this forum, I can post links to some books/external sources that cover this topic thoroughly.

|||

No, the order is pretty much meaningless (it is completely meaningless until you have tons of joins and tons of search arguments (tons being a scientific term meaning a lot for the hardware you are using.)

Where broadcast_date = '20060101'
and other_column = 'othervalue'

is identical to:

Where other_column = 'othervalue'
and broadcast_date = '20060101'

because they are mathematically the same, the optimizer can choose to evaluate them in any order.

|||Yup. When you have really big number of tables and conditions involved in your query, there are many indexes that might be used to resolve the query, then the optimizer might consider not looking all possible execution plans. This is due to the number of such plans can be enormously big, and iterating through these plans means that a significant amount of time might be spent just to choose the appropriate plan.
In such cases, the optimizer uses heuristics to prune the number of possible execution plans. These heuristics might be sensitive to the order of conditions etc. in the query code.|||

And I missed this part before:

P.S. If it is permitted in this forum, I can post links to some books/external sources that cover this topic thoroughly.

It is strongly encouraged to do this (as long as it isn't TOO self serving. Like just posting an answer that states "buy my book, it covers this" would seem wrong, but mentioning books, resources, etc that cover the topic along with some explanation is the best of all possiblities.) More things to read the better!

Of course linking to free resources as the answer to a question is often the best way to go to, there are a lot of good resources our there that answer the questions that have been asked by everyone at one time (me included :)

Louis

|||

I think your question is the following:

index on columns (a, b)

what happens if the WHERE clause is:

WHERE b = 2

In this case, the index cannot be used to efficiently seek to the matching rows. The distribution statistics are maintained only for the primary column that is specified in a multi-column index. So you need to specify at least that to be able to use the index. Otherwise, SQL Server will pick a plan that will scan the entire index/table to find the matching rows. Additionally, you can specify some bogus condition like:

where a > 0 and b = 2

which will force the index to be used indirectly. But this might not be a good option since it will anyway result in scanning the entire index and the plan that scans the table directly might be more beneficial.

|||

Hi ,

Its really good to see lots of responses. Thanks all.

So rading all the replies, I drawn a conclusion that, putting the sequence of where clause same as index will help if you have lots of join and lagrge table size.

Thanks & Rgds

vyanki

|||

Change will to may...

...same as index may help if you have...

any you have it. And it is not the table size, it is the complexity of the query. table sizing is all taken care of by statistics which are very fast to work with. It is just that as you add more and more joins, the possible permutations of orders to evaluate criterium grows very large and unwieldly to check every possible combination. And the likely first candidate for an order of execution will be the order you code things in. If this expected cost is less than the expected cost of doing more optimizations, it might just be that the plan will coincide with the order of the tables.

All this to say, don't worry about ordering of where or join clauses for the most part. This is almost never an issue (but it is one of those fun facts that it is good to have in your head when you need it.)

|||If I read your question it is possible you are asking the question that Umachandar answered rahter than the one Louis answered. This is critical. If you have only a concatinated index and you are not querying agaist the first element in the concatination then the index will not be used at all.

Also, the order of elements in the clauses of your SQL statement can alter the execution plan. In many cases the default plan is "good enough" but a hand tuned plan can have a huge performance impact. I have personally seen hand tuned SQL result in orders of magnitude response improvemnts.

|||

Hi,

Thanks for your suggestions.

But what I observed ,even if you have concateed index(having index on more than one field) the index get used when the first field in where clause is different than first field in index defination.

Thanks

Vyanki

|||Even if the index is used, it will be a scan not a seek. So you are essentially scanning the entire index (or table if it is clustered) to get the matching rows. You need to specify the primary column of the index to make use of efficient seeks.

About Indexes & Where caluse

Hi,

I am struggling with the sequence of parameters in my where clause.

In my databse table i have index on Broadcast_Date(some table field).The Index also include some other parameters which might become part of where clause.

The first field of the index is broadcast date.

So I want to know whether it is always compoulsory to have broadcast date as my first field of where clause.

Will it not scan the index if my where clause is starting with any other field which is also part of index.?

Will it hamper the query performance any way ?

Any help will really be appreciated.

Thanks

Vyanki

Hello,

the order of conditions in your where clause has no impact on index selection. Write in any way, it doesn't matter.

SQL chooses indexes(execution plan) based on the "logic" of your query and the information(statistics) that it available about the indexes that might be used to answer your query.
First, the SQL query is compiled into a "Query tree". This is a tree of algebraic operations, each of them has its corresponding SQL-counterpart.
Then, the query optimizer makes transformations of the query tree, and obtains new trees. These new trees are semantically equivalent to the initial tree, but they have different costs in terms execution.The cost of execution of each tree obtained is evaluated, based on index statistics. The one with the least cost is chosen as query plan.
Example. The query
select * from Table1 t1 join Table2 t2 on t1.fISN=t2.fISN join Table3 t3 on t2.fCODE=t3.fCODE
can be executed at least in 2 ways: first join Table2 and Table3, then join the resulting temporary table with Table1, or the other way around-join Table1 and Table2, then join the result with Table3. The optimiser considers both opportunities and chooses the "cheaper" one.

P.S. If it is permitted in this forum, I can post links to some books/external sources that cover this topic thoroughly.

|||

No, the order is pretty much meaningless (it is completely meaningless until you have tons of joins and tons of search arguments (tons being a scientific term meaning a lot for the hardware you are using.)

Where broadcast_date = '20060101'
and other_column = 'othervalue'

is identical to:

Where other_column = 'othervalue'
and broadcast_date = '20060101'

because they are mathematically the same, the optimizer can choose to evaluate them in any order.

|||Yup. When you have really big number of tables and conditions involved in your query, there are many indexes that might be used to resolve the query, then the optimizer might consider not looking all possible execution plans. This is due to the number of such plans can be enormously big, and iterating through these plans means that a significant amount of time might be spent just to choose the appropriate plan.
In such cases, the optimizer uses heuristics to prune the number of possible execution plans. These heuristics might be sensitive to the order of conditions etc. in the query code.|||

And I missed this part before:

P.S. If it is permitted in this forum, I can post links to some books/external sources that cover this topic thoroughly.

It is strongly encouraged to do this (as long as it isn't TOO self serving. Like just posting an answer that states "buy my book, it covers this" would seem wrong, but mentioning books, resources, etc that cover the topic along with some explanation is the best of all possiblities.) More things to read the better!

Of course linking to free resources as the answer to a question is often the best way to go to, there are a lot of good resources our there that answer the questions that have been asked by everyone at one time (me included :)

Louis

|||

I think your question is the following:

index on columns (a, b)

what happens if the WHERE clause is:

WHERE b = 2

In this case, the index cannot be used to efficiently seek to the matching rows. The distribution statistics are maintained only for the primary column that is specified in a multi-column index. So you need to specify at least that to be able to use the index. Otherwise, SQL Server will pick a plan that will scan the entire index/table to find the matching rows. Additionally, you can specify some bogus condition like:

where a > 0 and b = 2

which will force the index to be used indirectly. But this might not be a good option since it will anyway result in scanning the entire index and the plan that scans the table directly might be more beneficial.

|||

Hi ,

Its really good to see lots of responses. Thanks all.

So rading all the replies, I drawn a conclusion that, putting the sequence of where clause same as index will help if you have lots of join and lagrge table size.

Thanks & Rgds

vyanki

|||

Change will to may...

...same as index may help if you have...

any you have it. And it is not the table size, it is the complexity of the query. table sizing is all taken care of by statistics which are very fast to work with. It is just that as you add more and more joins, the possible permutations of orders to evaluate criterium grows very large and unwieldly to check every possible combination. And the likely first candidate for an order of execution will be the order you code things in. If this expected cost is less than the expected cost of doing more optimizations, it might just be that the plan will coincide with the order of the tables.

All this to say, don't worry about ordering of where or join clauses for the most part. This is almost never an issue (but it is one of those fun facts that it is good to have in your head when you need it.)

|||If I read your question it is possible you are asking the question that Umachandar answered rahter than the one Louis answered. This is critical. If you have only a concatinated index and you are not querying agaist the first element in the concatination then the index will not be used at all.

Also, the order of elements in the clauses of your SQL statement can alter the execution plan. In many cases the default plan is "good enough" but a hand tuned plan can have a huge performance impact. I have personally seen hand tuned SQL result in orders of magnitude response improvemnts.

|||

Hi,

Thanks for your suggestions.

But what I observed ,even if you have concateed index(having index on more than one field) the index get used when the first field in where clause is different than first field in index defination.

Thanks

Vyanki

|||Even if the index is used, it will be a scan not a seek. So you are essentially scanning the entire index (or table if it is clustered) to get the matching rows. You need to specify the primary column of the index to make use of efficient seeks.