Unusual Select Problem 1

[HoustonGuy]

I'm mentally stuck!

I have two tables.

TableOne has simple data like this:
Row_Id, StartRow, StopRow

TableTwo has data like this:
Row_id, String_of_Data

I need to select String_of_Data rows in TableTwo that fall between the StartRow and StopRow from TableOne in one resultset, so that the end result is a single column with all of TableTwo's String_of_Data values.

Sample Data:
TableOne
1 12 18
2 29 34
3 62 75
etc.

TableTwo
1 Stringofdata
2 Stringofdata
3 Stringofdata
etc

I think I'm gonna need to loop through TableOne and insert each iteration of TableTwo's String_of_Data into a final table. Each iteration will append the final table with the data to create one long column.

My description above probably sucks. :-( I can clarify if needed.
Thanks in advance for looking.

 

[gmmastros]

Select Table2.*
From   Table1
       Inner Join Table2
         On Table2.Row_Id Between Table1.StartRow And Table2.StopRow

If it's possible to have overlapping start and stops, then you may want to add Distinct to the query.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[HoustonGuy]

Thanks gmmastros - there are no overlapping starts and stops.

Did you mean:

On Table2.Row_Id Between Table1.StartRow And Table1.StopRow

 

[gmmastros]

Yes, that's what I meant.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[HoustonGuy]

Thanks gmmastros for such timely help!

I'm beginning to think this is best suited as a row based task, not a set based task.
The iterations through TableTwo make the processing real sloooow.

 

[gmmastros]

1. How many rows do you have in each table?
2. How many columns do you have in table2?
3. What indexes do you have on table2? Specifically, do you have an index with Row_Id and string_of_data (either as an index column or an include column)?


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[HoustonGuy]

About 5,000 in Table One and about 2 million in Table Two.
Checked indexes and there are none.

I should index Row_id and Stringdata and give it another go.

 

[HoustonGuy]

I'm indexing Row_ID and Stringofdata in Table Two as a nonclustered index with no non-key columns.
There are 5 columns in table two.

 

[gmmastros]

For table 2, I would create the index like this:

[tt]
Create Unique NonClustered Index idx_IndexNameHere On Table2(Row_Id) Include(String_of_data);[/tt]

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[HoustonGuy]

Awesome - running it now.

Is the power of this index implicit? Do I need to specifically reference it? The index will actually cover the query, since I'm using only the two indexed fields in my query. If I'm making sense.

 

[gmmastros]

When you run a query, several things occur.

First, the SQL engine will parse the query, checking for errors. Then it will determine the optimal way to execute the query. Finally it will execute the query and return the results.

Basically, SQL Server will see that there is an index that is covering the query so it will use the index.

I'm curious, how is the performance now? Is it acceptable?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[HoustonGuy]

Cool - I thought it was implicit, but I see some people use hints or inline references to the index itself. NOt sure if that would add to the speed.

I went from about 7,000 rows returned per hour to 80,000 rows returned per hour.
That is a tremendous improvement!

I cannot thank you enough!!