?
Solved

Slow Query

Posted on 2008-01-31
5
Medium Priority
?
170 Views
Last Modified: 2010-05-18
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.
0
Comment
Question by:matrix_aash
  • 2
  • 2
5 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20787759
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
 
LVL 10

Expert Comment

by:answer_me
ID: 20787836
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
 
LVL 10

Expert Comment

by:answer_me
ID: 20787850
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
 
LVL 8

Author Comment

by:matrix_aash
ID: 20787878
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1500 total points
ID: 20789052
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

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question