SQL Script Speed Question 1

[krisbrixon]

Which do you think would be faster.

Base information:
- Three tables
Table 1 has @ 100 records (i.e. Batch File)
Table 2 has @ 5000 records (i.e. Active File)
Table 3 has > 1,000,000 records (i.e. Historical File)
- The value I want will be in only one of the three tables.
- There is an 80% chance that it will be in table 2.
- There is a 1% chance that it will be in table 1.
- All three table have very similar structure.
- Using an Scalar recordset with VB.NET returning a SUM()
- SUM function will sum @ 1-20 records.
- A Stored Procedure is not a vaild option.

Option 1
- Join all three tables together and get the answer.

Option 2
- Run a statement on each of the tables and look at the results to see which one had the answer. In VB.NET setup a nested IF statment that will check each answer before if runs the next query and will stop running the queries if it finds one.
i.e.
If NOT "Run SQL on Table 2" > 0 Then
If NOT "Run SQL on Table 3" > 0 Then
If NOT "Run SQL on Table 1" > 0 Then
Return "value"
End If
Else
Return "value"
End If
Else
Return "value"
End If

Even if you do not know for sure, I would appreciate any semi-confident guesses.

Thanks,

Kris

 

[MeanGreen]

Use the UNION command to get what you want. Example:

create table #table1
(idno integer,
name varchar(25))
create table #table2
(idno integer,
name varchar(25))
create table #table3
(idno integer,
name varchar(25))

insert #table1 values(1,'Joe')
insert #table1 values(2,'John')
insert #table1 values(3,'Jerry')
insert #table1 values(4,'Jet')
insert #table2 values(5,'Sam')
insert #table2 values(6,'Sandy')
insert #table2 values(7,'Saul')
insert #table3 values(8,'Andy')

select idno,name from (
select idno,name from #table1
UNION ALL
select idno,name from #table2
UNION ALL
select idno,name from #table3
) t1
where t1.idno = 3

Hope this helps.

 

[MeanGreen]

I forgot that you had a very large table. Since you have a VERY LARGE table (1,000,000 rows plus), you might want to incorporate the where clause within each subselect to reduce time involved.

select idno,name from (
select idno,name from #table1
where idno = 3
UNION ALL
select idno,name from #table2
where idno = 3
UNION ALL
select idno,name from #table3
where idno = 3
) t1

Hope this helps.

 

[TerryStover]

Look up "partitioned views" in books online. If you can create a check constraint on the column / columns you used to partition your data into the 3 tables, the optimizer is smart enough to only hit the one table that contains the data. Under certain conditions the view is updatable as well. This is poor man's partitioning, works on Oracle too.

 

[krisbrixon]

Thanks MeanGreen.

I will be using my option two for my specified problem, but I will be using the UNION ALL for some other queries that hit all the tables.


Kris

p.s. thanks for the idea TerryStover, but for my situation I need to keep all the functionality in the code.

 

[SQLSister]

Personally I would union the two small tables together and query first and then only query the large table if the result is not found. This would save time the majority of the time and won't cost much time in the case where you eventually have to go to the bigger table.

With a table of over a million records it is a bad idea to slow the process of querying by refusing to use stored procedures. Ad hoc queries are slower and you will eventually have a noticable performance problem. I realize this is probably some sort of company poicy, but the decision makers need to be aware that this decision is a bad one when it comes to optimizing performance.