Solved

Query too slow comparing strings

Posted on 2013-01-24
3
313 Views
Last Modified: 2013-01-28
Hello, I have a query that returns a table, checking whether the id of the main table exists in some others... The problem i think is that this id is a guid (varchar(36)), and this "IN" statement for a 36 length string could be to blame for the slow query.

SELECT "FALSE" AS HitMeXTremeSelected, HitMeXTremeRegisterID AS MHitMeXTremeRegisterID,HitMeXTremeNewRegisterID AS MHitMeXTremeNewRegisterID,
HitMeXTremeClosed AS MHitMeXTremeClosed,HitMeXTremeExcluded AS MHitMeXTremeExcluded,HitMeXTremeImportDate AS MHitMeXTremeImportDate,
hit9e7663f93614481a9a01d6e0d70b71aa AS Mhit9e7663f93614481a9a01d6e0d70b71aa,hit7846a637579e441286d824baaa619c63 AS Mhit7846a637579e441286d824baaa619c63,
hitbd5da3f8c5f1418194643a62db4abbf7 AS Mhitbd5da3f8c5f1418194643a62db4abbf7,hit7c45226d7d594edb83574254d739f2e2 AS Mhit7c45226d7d594edb83574254d739f2e2,
hit5f6e65a503464ef7965ef091b015d2ba AS Mhit5f6e65a503464ef7965ef091b015d2ba,hit07bbbc69ccc94d3a9e8a3c70a3f4e682 AS Mhit07bbbc69ccc94d3a9e8a3c70a3f4e682,
hit2dd2849c721e43df8271eb6dbfaa9a3a AS Mhit2dd2849c721e43df8271eb6dbfaa9a3a,hit42c22f7eca7643e9b01c583f52330e12 AS Mhit42c22f7eca7643e9b01c583f52330e12,
hit9fc32e9c62384854904ed4ccb8a41585 AS Mhit9fc32e9c62384854904ed4ccb8a41585,hit722b407b32124e6ca01b184dd934c4bc AS Mhit722b407b32124e6ca01b184dd934c4bc,
hit77906e7151944f0597465631a3f1b47a AS Mhit77906e7151944f0597465631a3f1b47a,hit0cabe711e80b479a958aba2ffea353d6 AS Mhit0cabe711e80b479a958aba2ffea353d6,
hitdec626278ab941ea852b0428e914cf22 AS Mhitdec626278ab941ea852b0428e914cf22,hit21007e6d45cc4515862a9b2b93b8d976 AS Mhit21007e6d45cc4515862a9b2b93b8d976,
hit425b903eb494443794b06bde1609d69a AS Mhit425b903eb494443794b06bde1609d69a,hitcd678699985841dfae592320f35b7a04 AS Mhitcd678699985841dfae592320f35b7a04,
hit380ab1155a46465d9ba326b6ee78131d AS Mhit380ab1155a46465d9ba326b6ee78131d 
FROM tbldb01745c33005641a4817fea2e246f2b26 
WHERE HitMeXTremeNewRegisterID = "" 
AND HitMeXTremeRegisterID NOT IN (SELECT id_MasterRegister FROM dbHitData.tblMatched 
				WHERE id_MasterRegister IS NOT NULL 
				AND id_Match NOT IN (SELECT id_Match FROM dbHitData.tblRejected) 
				AND id_Mirror IN (SELECT id_Mirror FROM dbHitSystem.tblMirrors WHERE id_Procedure = '63e926ab-735c-411a-afea-92e573088a34'))  
AND HitMeXTremeRegisterID NOT IN (SELECT id_Register FROM dbHitData.tblExcluded WHERE id_Procedure = '63e926ab-735c-411a-afea-92e573088a34')

Open in new window


I know the basics of SQL, but is there any way I can improve the performance of this query??

Thanks
0
Comment
Question by:toddinho
3 Comments
 
LVL 45

Accepted Solution

by:
Kdo earned 300 total points
ID: 38816197
Hi Todd,

Can you tell me a bit about the data?

If the number of rows where HitMeXTremeNewTegisterID="" is small, selecting those rows and outer joining the other tables to it should be very fast.   Other data patterns may do better with different SQL.

And of course, the proper indexes are critical.


Kent
0
 
LVL 29

Assisted Solution

by:fibo
fibo earned 200 total points
ID: 38824409
selecting "not in" is usually slow, and string comparisons may be quite slow too.

as suggested, have you indexed these string fields? this might improve things a lot...
0
 

Author Comment

by:toddinho
ID: 38827026
Yeah, indexing the columns was the way to go... Thanks for the quick answers!!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

744 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now