SQL Server – where clause

Tags:
SQL Server
SQL Server performance
WHERE clause
When I execute query like below select * from A where bb in ('1','2','3') That is very fast. not a problem. But in procedure first, if I make a "B" table with '1','2','3' and join with A table like below, select * from a a , B b where a.xx = b.xx and than it is very slow. Do you know why and how to solve it?
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

First you should get used to using the new syntax for writing joins as eventually the old syntax (which you used) will no longer be supported.

<pre>SELECT *
FROM a
INNER JOIN b ON a.xx = b.xx</pre>

As for why this is slow you probably don’t have the correct indexes on table b. Also if there are a lot of records is each table all those records have to be transferred to your client.

Because you have the SELECT * in there, if either table has the clustered index created on a column other than xx SQL will have to do either a clustered index scan (which is slow) or a Key lookup (which is also slow) to find all the data. Recreate an index on on both tables on the column xx and include the columns you need to return to the client. Then change the SELECT * to return only the columns you need.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Kccrosser
    Note that comparing an interactive query with a literal expression to a similar query using live data at run time is always questionable. 1. Your original query has a literal expression ("... in ('1','2','3')"). When you run that interactively, the query optimizer can choose to convert the literal query to the equivalent (but much faster) version of select ... where bb = '1' union all select ... where bb = '2' union all select ... where bb = '3' Assuming that column is indexed, and there is a reasonable distribution of values in the column, this can be a very fast query. 2. When you compile a query that takes the value arguments at run time, the query optimizer doesn't know how to construct the query a priori, so it will try its best at run time, but will usually generate a less efficient query than one containing literal values. 3. If you just created the table and haven't set up everything right, new tables (or tables with lots of new data) may not be analyzed and/or may have "stale" statistics. When one or more tables in a query have not been analyzed recently, the query optimizer may drop back into table scan mode - even when there are tables in the query that HAVE been optimized. It is usually very good practice to make sure all tables in a query have been recently analyzed to establish the value distributions (which is how the query optimizer decides how to execute the query). 4. You don't specify which version of SQL Server you are using. If it is earlier than SQL 2005, or if (for some reason) automatic updating of statistics is not enabled, the query optimizer can be very confused and make bad decisions. If in doubt, try running "update statistics dbo.<table>". You may also want to turn on automatic statistics updating, if it is off. If you are not the DBA, you should verify this with your DBA before changing such settings (assuming you have the rights). Updating statistics on large production tables during production operations may cause various performance problems. 5. Statistics are only compiled on columns that are indexed. If you created this new table B and didn't create an index on the column containing the values of interest, there won't be any statistics on the value distribution in that column. Creating an index on that column may resolve the performance issue.
    3,830 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: