Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Query really slow with eager spool Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, January 10, 2007 9:38 AM
 Grasshopper 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.pdfSo it can have 8 or 9 digits.Sorry for the 256, it's obviously 255!ThanksStephane
Post #335856
 Posted Wednesday, January 10, 2007 10:02 AM
 SSCarpal Tunnel Group: General Forum Members Last Login: Today @ 2:45 AM Points: 4,863, Visits: 8,385
 StephaneOK, 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 rset countryId = c.countryIdfrom #regions r join tbl_ip2countries con r.ipFloat between c.ipFrom and c.ipToNot 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 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 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?ThanksStephane
Post #335883
 Posted Wednesday, January 10, 2007 10:38 AM
 SSC-Insane Group: General Forum Members Last Login: Friday, June 21, 2013 1:53 PM Points: 21,376, Visits: 9,590
 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
 SSCarpal Tunnel Group: General Forum Members Last Login: Today @ 2:45 AM Points: 4,863, Visits: 8,385
 StephaneThe 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 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 rset countryId = c.countryIdfrom #regions r join tbl_ip2countries c WITH (INDEX(PK_tbl_ip2countries))on r.ipFloat between c.ipFrom and c.ipToDoes not improve anything... Thanks for your time guys!Stephane
Post #335927
 Posted Wednesday, January 10, 2007 12:44 PM
 Grasshopper 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
 Grasshopper Group: General Forum Members Last Login: Today @ 3:38 AM Points: 19, Visits: 77
 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 Group: General Forum Members Last Login: Today @ 1:56 AM Points: 6,278, Visits: 12,094
 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 ShawFor 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 ModenExploring Recursive CTEs by Example Dwain Camps
Post #1478589

 Permissions