Click here to Skip to main content
15,889,216 members
Home / Discussions / Database
   

Database

 
AnswerRe: Slow query when using @variable in Where clause Pin
Ben Fair27-Jan-09 4:43
Ben Fair27-Jan-09 4:43 
GeneralRe: Slow query when using @variable in Where clause Pin
Meysam Mahfouzi27-Jan-09 8:21
Meysam Mahfouzi27-Jan-09 8:21 
GeneralRe: Slow query when using @variable in Where clause Pin
Ben Fair27-Jan-09 10:50
Ben Fair27-Jan-09 10:50 
AnswerRe: Slow query when using @variable in Where clause Pin
Wendelius27-Jan-09 4:58
mentorWendelius27-Jan-09 4:58 
GeneralRe: Slow query when using @variable in Where clause Pin
Meysam Mahfouzi27-Jan-09 8:14
Meysam Mahfouzi27-Jan-09 8:14 
GeneralRe: Slow query when using @variable in Where clause Pin
Wendelius27-Jan-09 8:36
mentorWendelius27-Jan-09 8:36 
GeneralRe: Slow query when using @variable in Where clause Pin
Meysam Mahfouzi27-Jan-09 9:09
Meysam Mahfouzi27-Jan-09 9:09 
GeneralRe: Slow query when using @variable in Where clause Pin
Wendelius27-Jan-09 9:34
mentorWendelius27-Jan-09 9:34 
That's what I suspected. And now we're actually talking about a whole different story.

Now if you modify the query and use a date column, for example something like:
DECLARE @c TINYINT
SET @c = 0
SELECT TOP 10 * 
FROM News
WHERE Culture = @c
ORDER BY AdditionDate

you need to add a new index to your table on columns Culture, AdditionDate. If you make that modification to your query, your filter factor will be dramatically different so the index will be efficient.

There's a simple rule of thumb (which is correct in most of the cases). If the filter factor regarding an index is less than 5%, using index is efficient. Above that it's not efficient. So in your first example if you had 4 different cultures and let's say that they were evenly distributed, the filter factor was 25%. So in that case using the index (culture) is going to be a very poor solution by the optimizer. But if it does use the index in that scenario (as I suspect it did) you will suffer from poor performance. In such cases actually removing the index makes the performance better (unless the index is used in other scenarios). The reasons why some of your test were performing better was because the data was unevenly distributed.

So what I suggest is that you first modify the query to the final version and begin optimization after that (if needed anymore). What I said about UPDATE STATISTICS is still a valid statement.

The need to optimize rises from a bad design.My articles[^]

GeneralRe: Slow query when using @variable in Where clause [modified] Pin
Meysam Mahfouzi27-Jan-09 20:40
Meysam Mahfouzi27-Jan-09 20:40 
GeneralRe: Slow query when using @variable in Where clause Pin
Wendelius27-Jan-09 20:49
mentorWendelius27-Jan-09 20:49 
GeneralRe: Slow query when using @variable in Where clause Pin
Meysam Mahfouzi27-Jan-09 21:02
Meysam Mahfouzi27-Jan-09 21:02 
GeneralRe: Slow query when using @variable in Where clause Pin
Wendelius27-Jan-09 21:28
mentorWendelius27-Jan-09 21:28 
GeneralRe: Slow query when using @variable in Where clause Pin
Meysam Mahfouzi27-Jan-09 22:41
Meysam Mahfouzi27-Jan-09 22:41 
GeneralRe: Slow query when using @variable in Where clause Pin
Wendelius27-Jan-09 22:59
mentorWendelius27-Jan-09 22:59 
GeneralRe: Slow query when using @variable in Where clause Pin
Meysam Mahfouzi27-Jan-09 23:11
Meysam Mahfouzi27-Jan-09 23:11 
GeneralRe: Slow query when using @variable in Where clause Pin
Wendelius27-Jan-09 23:23
mentorWendelius27-Jan-09 23:23 
GeneralRe: Slow query when using @variable in Where clause Pin
Meysam Mahfouzi27-Jan-09 23:59
Meysam Mahfouzi27-Jan-09 23:59 
GeneralRe: Slow query when using @variable in Where clause Pin
Wendelius28-Jan-09 0:21
mentorWendelius28-Jan-09 0:21 
GeneralRe: Slow query when using @variable in Where clause Pin
Meysam Mahfouzi28-Jan-09 1:07
Meysam Mahfouzi28-Jan-09 1:07 
GeneralRe: Slow query when using @variable in Where clause Pin
Wendelius28-Jan-09 1:19
mentorWendelius28-Jan-09 1:19 
GeneralRe: Slow query when using @variable in Where clause Pin
Meysam Mahfouzi28-Jan-09 1:53
Meysam Mahfouzi28-Jan-09 1:53 
GeneralRe: Slow query when using @variable in Where clause Pin
Wendelius28-Jan-09 2:13
mentorWendelius28-Jan-09 2:13 
GeneralRe: Slow query when using @variable in Where clause Pin
Meysam Mahfouzi28-Jan-09 2:56
Meysam Mahfouzi28-Jan-09 2:56 
GeneralRe: Slow query when using @variable in Where clause Pin
Wendelius28-Jan-09 3:12
mentorWendelius28-Jan-09 3:12 
GeneralRe: Slow query when using @variable in Where clause Pin
Meysam Mahfouzi28-Jan-09 3:31
Meysam Mahfouzi28-Jan-09 3:31 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.