Slow Query

Hi All,

I am running the following query and it's taking long time to displace results:

SELECT DISTINCT((SELECT COUNT(IMGTABLE.BATCHID) FROM IMGTABLE,BATCHTABLE WHERE
IMGTABLE.BATCHID = BATCHTABLE.BATCHID AND BATCHTABLE.BATCHNAME LIKE '%31-01-08%')
+ (SELECT COUNT(BARCODETABLE.BARDATA) FROM BARCODETABLE,BATCHTABLE
WHERE BARCODETABLE.BATCHID = BATCHTABLE.BATCHID AND BATCHTABLE.BATCHNAME LIKE
'%31-01-08%' AND BARCODETABLE.BARDATA = 'PREVIEWSEP'))
FROM IMGTABLE,BARCODETABLE,BATCHTABLE
GROUP BY IMGTABLE.BATCHID,BARCODETABLE.BATCHID,BARCODETABLE.BARDATA

Can sombebody please help me with this?

Thanks,

Aash.
LVL 8
matrix_aashAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
please attached the explain plan, and tell us what indexes you have on the tables.
a first thing is the LIKE '%...%' are "killers", ie cannot use any index.
what is the contents of the field batchname in general, could you get the date part into a (computed) column, so it could be indexed?
0
answer_meCommented:
Try this:

SELECT BATCHTABLE.BATCHID, SUM(CASE IMGTABLE.BATCHID IS NULL THEN 0 ELSE 1 END), SUM(CASE BARCODETABLE.BATCHID IS NULL THEN 0 ELSE 1 END)
FROM BATCHTABLE
LEFT JOIN IMGTABLE ON IMGTABLE.BATCHID = BATCHTABLE.BATCHID AND BATCHTABLE.BATCHNAME LIKE '%31-01-08%'
LEFT JOIN BARCODETABLE BARCODETABLE.BATCHID = BATCHTABLE.BATCHID AND BATCHTABLE.BATCHNAME LIKE
'%31-01-08%' AND BARCODETABLE.BARDATA = 'PREVIEWSEP'
GROUP BY BATCHTABLE.BATCHID

Open in new window

0
answer_meCommented:
Sorry the earlier one had syntax errors.
SELECT BATCHTABLE.BATCHID, SUM(CASE WHEN IMGTABLE.BATCHID IS NULL THEN 0 ELSE 1 END), SUM(CASE WHEN BARCODETABLE.BATCHID IS NULL THEN 0 ELSE 1 END)
FROM BATCHTABLE
LEFT JOIN IMGTABLE ON IMGTABLE.BATCHID = BATCHTABLE.BATCHID AND BATCHTABLE.BATCHNAME LIKE '%31-01-08%'
LEFT JOIN BARCODETABLE ON BARCODETABLE.BATCHID = BATCHTABLE.BATCHID AND BATCHTABLE.BATCHNAME LIKE '%31-01-08%' AND BARCODETABLE.BARDATA = 'PREVIEWSEP'
GROUP BY BATCHTABLE.BATCHID

Open in new window

0
matrix_aashAuthor Commented:
The batchname column has the following format : date - b_00000_000_000
Above is the general format of batchname column

date is stored as dd-mm-yy and then a hypen and then b_ and a five digit number which is also used for search criteria and than _000 and _000 meaning 3 digit numbers.
The indexes are on column batchtable.batchid,batchtable.batchname,imgtable.batchid.

Hope this helps

Aash.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
so if the batchname starts with the date, remove the % leading the value:

BATCHTABLE.BATCHNAME LIKE '31-01-08%'

will work better, assuming there is an index on that field.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.