February 7, 2014 at 1:40 am
These two queries give the same result. Which of both is the best?? Is ther a way to avoid or make better the 'OR'-phrase ( (l3='s' OR l3='d')OR (l4='s' OR l4='d')OR (l5='s' OR l5='d')OR (l6='s' OR l6='d')) ??
Thxs,
SELECT dlid, dldoel, dlprefix, dlleerlijn, l1, l2, l3, l4 , l5 , l6
FROM dldoelen
WHERE dlid
LIKE 'DL%'
AND ( (l3='s' OR l3='d')OR (l4='s' OR l4='d')OR (l5='s' OR l5='d')OR (l6='s' OR l6='d'))
AND dlid IN (SELECT agClid FROM dlagenda2
WHERE (agSchoolid='18' and agLkrid='1' and agDatum >= '2013-09-02' and agDatum <= '2014-02-07' ))
SELECT DISTINCT dlid, dldoel, dlprefix, dlleerlijn, l1, l2, l3, l4 , l5 , l6
FROM dlagenda2 JOIN dldoelen
ON dlid = agClId
WHERE
dlid LIKE 'DL%'
and agSchoolid='18' and agLkrid='1'
and agDatum >= '2013-09-02' and agDatum <= '2014-02-07'
AND ((l3='s' OR l3='d')OR (l4='s' OR l4='d')OR (l5='s' OR l5='d')OR (l6='s' OR l6='d'))
February 7, 2014 at 8:06 am
Please post your execution plan (you can obtain this by pressing "Ctrl-M" before running your query. Also, obtain the STATISTICS IO and TIME for the query (for each one) - this will give you the amount of time in miliseconds and the amount of reads each query takes. These 2 things will help you greatly in determining which method works the best (i.e. low IO and time should indicate better performance)
Example:
SET STATISTICS IO, TIME ON;
SELECT
dlid,
dldoel,
dlprefix,
dlleerlijn,
l1,
l2,
l3,
l4,
l5,
l6
FROM
dldoelen
WHERE
dlid LIKE 'DL%'
AND ((l3 = 's'
OR l3 = 'd')
OR (l4 = 's'
OR l4 = 'd')
OR (l5 = 's'
OR l5 = 'd')
OR (l6 = 's'
OR l6 = 'd'))
AND dlid IN (SELECT
agClid
FROM
dlagenda2
WHERE
(agSchoolid = '18'
AND agLkrid = '1'
AND agDatum >= '2013-09-02'
AND agDatum <= '2014-02-07'))
SELECT DISTINCT
dlid,
dldoel,
dlprefix,
dlleerlijn,
l1,
l2,
l3,
l4,
l5,
l6
FROM
dlagenda2
JOIN dldoelen
ON dlid = agClId
WHERE
dlid LIKE 'DL%'
AND agSchoolid = '18'
AND agLkrid = '1'
AND agDatum >= '2013-09-02'
AND agDatum <= '2014-02-07'
AND ((l3 = 's'
OR l3 = 'd')
OR (l4 = 's'
OR l4 = 'd')
OR (l5 = 's'
OR l5 = 'd')
OR (l6 = 's'
OR l6 = 'd'))
SET STATISTICS IO, TIME OFF;
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 7, 2014 at 1:52 pm
And, if the query uses more than one table, use an alias and prefix all columns with the correct alias name. Remember, we have absolutely no idea what columns are in what tables.
For example -- just an example, I don't know which columns are in which table:
SELECT DISTINCT alias1.dlid, alias1.dldoel, alias1.dlprefix, alias1.dlleerlijn, alias2.l1, alias2.l2, alias2.l3, alias2.l4 , alias2.l5 , alias2.l6
FROM dlagenda2 AS alias1
JOIN dldoelen AS alias2
...and so on...
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 7, 2014 at 3:57 pm
Try This SELECT DISTINCT dlid, dldoel, dlprefix, dlleerlijn, l1, l2, l3, l4 , l5 , l6
FROM dlagenda2
JOIN dldoelen ON dlid = agClId
AND dlid LIKE 'DL%'
AND agSchoolid='18'
AND agLkrid='1'
AND agDatum >= '2013-09-02'
AND agDatum <= '2014-02-07'
AND (
(l3 IN ('s','d'))
OR (l4 IN ('s','d'))
OR (l5 IN ('s','d'))
OR (l6 IN ('s','d'))
)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply