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

108,000,000 row table - check for matches Expand / Collapse
Author
Message
Posted Monday, September 23, 2013 11:33 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 4:44 PM
Points: 8,286, Visits: 8,736
bleroy (9/23/2013)
What about adding 1 column to the small table that would contain RIGHT(smallTable.toid,13), create an index on that and then do your join?

B

I wish I'd said it in those words instead of adding more detail (like not looking at the trailing 13 characters if the first 3 were not one of the two specified triples, and not including things that were matched on all 16 characters because they would already have been counted as matched) - as I said I was trying to simplify he query I should have kept the description simple as well.


Tom
Post #1497475
Posted Monday, September 23, 2013 11:53 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, April 05, 2014 1:16 AM
Points: 14, Visits: 203
L' Eomot Inversé (9/23/2013)
bleroy (9/23/2013)
What about adding 1 column to the small table that would contain RIGHT(smallTable.toid,13), create an index on that and then do your join?

B

I wish I'd said it in those words instead of adding more detail (like not looking at the trailing 13 characters if the first 3 were not one of the two specified triples, and not including things that were matched on all 16 characters because they would already have been counted as matched) - as I said I was trying to simplify he query I should have kept the description simple as well.


Thank you for your valuable contribution, this forum is fantastic! Best regards, D.
Post #1497487
Posted Tuesday, September 24, 2013 5:59 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 4:43 AM
Points: 756, Visits: 631
A small addiiton in case it was not clear: add that colimn as a computed column to the able and index it. That saves some space over adding it as a regular column.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1497784
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse