| Author |
Topic |
|
Lumbago
Norsk Yak Master
3271 Posts |
 Posted - 2004-09-16 : 08:57:23
|
| I have an update statement which basically updates a table based on some summed money-columns. The update has something like this in the end:...WHERE UserID = @UserID_1 OR UserID = @UserID_2GROUP BY UserIDI used to have just "WHERE UserID = @UserID" which used a nice Clustered index seek, but when I added the "OR UserID = @UserID_2" the plan changed into a scan. Is there anything I can do to have t use a seek instead? I have tried with "UserID IN (@UserID_1, @UserID_2)" also but without much luck.--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
gpl
Posting Yak Master
195 Posts |
Posted - 2004-09-16 : 09:28:07
|
| Try popping your user ids into a temp table (with index) - then join your main table with itGraham |
 |
|
|
samsekar
Constraint Violating Yak Guru
437 Posts |
Posted - 2004-09-16 : 09:57:36
|
| How many records your query returns ? if it is more relative to the total no of records, then clustered index scan is prefered.- Sekar |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-09-16 : 10:00:14
|
| well, it depends...could be anything from 0 to a few hundred...--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
kselvia
Aged Yak Warrior
526 Posts |
Posted - 2004-09-16 : 13:40:40
|
| You can split the query into two queries and they will get different plans...WHERE UserID = @UserID_1 GROUP BY UserIDUNION ALL...WHERE UserID = @UserID_2GROUP BY UserID--KenI want to die in my sleep like my grandfather, not screaming in terror like his passengers. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-09-17 : 05:01:55
|
| But Ken, if I use the UNION ALL woun't the query process actually be run twice, once for each and then joined together? I would think that this would be worse performancewise but I really don't know.--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-09-17 : 05:06:00
|
have you tried using an index hint?just an idea... |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-09-17 : 05:12:14
|
| Hm, haven't tried it in this case actually but it might be worth a shot. I try to avoid them but maybe it will do some good here...I'll let you know :)--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
kselvia
Aged Yak Warrior
526 Posts |
Posted - 2004-09-17 : 12:24:55
|
quote:
Originally posted by Lumbago
But Ken, if I use the UNION ALL woun't the query process actually be run twice, once for each and then joined together? I would think that this would be worse performancewise but I really don't know.
Yes it would be run twice but that might be the most efficient way to do it. The only to know is to try it. From http://www.sql-server-performance.com/transact_sql.aspquote:
A query with one or more OR clauses can sometimes be rewritten as a series of queries that are combined with a UNION ALL statement, in order to boost the performance of the query. For example, let's take a look at the following query:SELECT employeeID, firstname, lastnameFROM namesWHERE dept = 'prod' or city = 'Orlando' or division = 'food' This query has three separate conditions in the WHERE clause. In order for this query to use an index, then there must be an index on all three columns found in the WHERE clause.This same query can be written using UNION ALL instead of OR, like this example:SELECT employeeID, firstname, lastname FROM names WHERE dept = 'prod'UNION ALLSELECT employeeID, firstname, lastname FROM names WHERE city = 'Orlando'UNION ALLSELECT employeeID, firstname, lastname FROM names WHERE division = 'food' Each of these queries will produce the same results. If there is only an index on dept, but not the other columns in the WHERE clause, then the first version will not use any index and a table scan must be performed. But in the second version of the query will use the index for part of the query, but not for all of the query.
--KenI want to die in my sleep like my grandfather, not screaming in terror like his passengers. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-09-17 : 14:25:03
|
| I don't know about the article itself, but that's a bad example to use. The queries shown are not equivalent -- with the UNION ALL it will return duplicate rows for a situation in which more than 1 part of the WHERE clause is true. And if you switch to UNION instead of UNION ALL, you get a big performance hit. So I am not sure that you can justify using a UNION in that specific example -- in fact, you SHOULDN'T -- the results will not be what you intended.- Jeff |
|
|
|
kselvia
Aged Yak Warrior
526 Posts |
Posted - 2004-09-17 : 14:32:24
|
| Hmm Jeff is right. The technique is only useful where the criteria are mutually exclusive (as in your example) I knew I had read an article about it and I just pasted their example, but as Jeff noted - that is a bad example. I'm a bit suprised. They are usually correct over there at sql-server-performance.com--KenI want to die in my sleep like my grandfather, not screaming in terror like his passengers. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-09-18 : 06:32:38
|
| I expect tou know this, but.I stickSET STATISTICS IO ON; SET STATISTICS TIME ON... stuff here ...SET STATISTICS IO OFF; SET STATISTICS TIME OFFaround the bits I want to test, then I look at the Scan Count and Logical Reads - and try to get them smaller!Kristen |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-09-18 : 07:07:04
|
| You should not expect anything with a semirookie like myself, but I have given them a glance or two :) Any rules of thumb I should follow? A wise man once told me something in the area of less than 4 logical reads per scan count or something...And another thing: how important is it to have alot of actual data in the tables while testing? I usually fill the tables manually with up to maybe 30 rows just to test if my queries actually get the data they are supposed to, and then look at indexes and plans to decide their efficiency. Would it be better if I filled them with a few million rows? Should I expect any different results?--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-09-18 : 07:14:59
|
| DEV is one thing, TEST another - IMHO.For TEST I want a copy of the real data. Then I can fine tune the queries. You may very well get a different query plan with lots of data.I don't have a hard and fast rule for what the numbers should be, but the moment the indexes don't get used the Logical Reads goes through the roof :-)So I find this method useful for twiddling with the query and seeing how the logical reads adjust. I suggest you try each of the suggestions here and keep an eye on the Logical Reads for each one."Would it be better if I filled them with a few million rows? Should I expect any different results?"I think the key to this is that the real data may change the granularity of the data, that in turn may cause the query planner to chosoe a different method.Kristen |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-09-18 : 07:39:42
|
| Ok, so since there is no difference between development and test (we are only a tiny company) you recomend that I first do the development, and when I am confident that the database design is correct I should fill the tables with data and then do additional testing?--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-09-18 : 10:25:00
|
| That would be my plan. As you are small and if the data is readily available I'd put that in my DEV system (if security is not an issue amongst the developers, AND you are not going to accidenatlly EMail the actual user's email address etc.!!)Kristen |
|
|
|
|
WHERE UserID IN (...)