Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Query really slow with eager spool Expand / Collapse
Author
Message
Posted Wednesday, January 10, 2007 9:38 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, March 10, 2007 12:58 PM
Points: 16, Visits: 1

Hi,

Thanks for your fast answer!

The ip is converted this way: For example, If the IP address 161.132.13.1, then the IP number is 2709785857.
IP Number, X = 161 x (256*256*256) + 132 x (256*256) + 13 x (256) + 1 = 2709785857.

You can have more info here: http://www.ip2location.com/docs/IP2Location_IP_Country_Region_City_Specification.pdf

So it can have 8 or 9 digits.

Sorry for the 256, it's obviously 255!

Thanks

Stephane

Post #335856
Posted Wednesday, January 10, 2007 10:02 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:17 AM
Points: 5,230, Visits: 9,457

Stephane

OK, that clears up the IP conversion algorithm.  I still don't think that your sample data from tbl_ip2countries and from #regions tally up with each other, but let's not worry about that for now.

Try this:

update r
set countryId = c.countryId
from #regions r join tbl_ip2countries c
on r.ipFloat between c.ipFrom and c.ipTo

Not sure how it would perform, but much better than a cursor, I should think.  You could experiment with using >= and <= operators instead of the BETWEEN function to see if that goes any faster.

John

Post #335871
Posted Wednesday, January 10, 2007 10:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, March 10, 2007 12:58 PM
Points: 16, Visits: 1

hum...

That's really weird: For 10 ip, it takes 7-8 seconds. For 100, it takes about a minute which is slower than my problem at the beginning.

BUT!

If I try with 40 or 50 ip, it executes in a flash: 1 second!

The difference is in uses table spool (lazy spool) in the first case but only clustered index seek in the second case.

What the ...???

By the way, in the prod environment, it's going to be up to 1000 ip.

Stephane

Post #335877
Posted Wednesday, January 10, 2007 10:34 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, March 10, 2007 12:58 PM
Points: 16, Visits: 1

I really don't get it...

10-20-30 ip uses the lazy spool so it takes several seconds.

40-50-60 ip uses the clustered index seek so it executes in a flash.

70 and more also uses the lazy spool so it takes more than a minute.

Is there a way to force the use of the index instead of the costly table spool?

By the way, it's a clustered index SCAN with the table spool instead of a clustered index seek. What's the difference?

Thanks

Stephane

Post #335883
Posted Wednesday, January 10, 2007 10:38 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:08 PM
Points: 21,385, Visits: 9,602

SCAN  : reads all rows.

SEEK :  binary search algorithm to find only the required row.

 

Could you reindex and / or update the statistics on the table.

 

If that doesn't help you could always use index and join hints (last resort).

Post #335886
Posted Wednesday, January 10, 2007 10:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:17 AM
Points: 5,230, Visits: 9,457

Stephane

The scan is scanning the whole index (the whole table in the case of a clustered index) whereas a seek goes straight to where it needs to go to find the data.  Therefore a seek is usually desirable.  I think the reason for this is that the query optimizer can't use the clustered index to work out the "<= ipTo" part of the "BETWEEN ipFrom and ipTo" clause.  Therefore I would recommend you create a non-clustered index on ipTo (assuming that your clustered index is created on ipFrom and ipTo in that order).

John

Post #335888
Posted Wednesday, January 10, 2007 11:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, March 10, 2007 12:58 PM
Points: 16, Visits: 1

Geez...

Still the same results. Works great for 40-50-60, but not for any other number...

I tried this hint:

update r

set countryId = c.countryId

from #regions r join tbl_ip2countries c WITH (INDEX(PK_tbl_ip2countries))

on r.ipFloat between c.ipFrom and c.ipTo

Does not improve anything...

Thanks for your time guys!

Stephane

Post #335927
Posted Wednesday, January 10, 2007 12:44 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, March 10, 2007 12:58 PM
Points: 16, Visits: 1

I made few more tests and It's working great with few hundreds ip. It's really fast.

I'll stick with that from now on!

Thanks a lot!

Stephane

Post #335965
Posted Monday, July 29, 2013 7:19 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 6:37 AM
Points: 66, Visits: 178
Hi,

Here is where the problem:
update #regions set countryId = (select countryId from tbl_ip2countries where ipFloat between ipFrom and ipTo)


The above update statment will be executed on (roughly 2000 rows as a sample) all the rows of the temp table whereas it reads the data from the base table which is about 2 million records, hence it would read the records from the base table on each row of the temp table, thus it will use the tempdb for a better performace, the Index spool is happening, why don't you try this with a inner join, so it can search for exact rows and make it faster..

Thanks,
Prabhu
Post #1478575
Posted Monday, July 29, 2013 7:41 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:56 AM
Points: 7,120, Visits: 13,491
This thread is six years old.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1478589
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse