SQL Speed - Tables vs. Views

[tbg130]

Hi,

I've been experimenting with tables and searches and their corresponding times for searches.

Orginally, I had a table with about 2500 results in it; when seaching for things, I was getting results in 20 milliseconds (according to the SQL Profiler).

I've since taken that data and broken it out to 3 tables so that I could ensure uniqueness of data (no duplicates of certain fields as I couldn't do 'unique but allow nulls'...

Now, the exact same query against a View of the same data (about 2500 records) takes about 200 milliseconds; nearly 10 times the duration.

Any reason for this? Am I doing something wrong?

Help is greatly appreciated.

Thanks in advance.

 

[unclerico]

When you introduce JOIN's into the equation there is more overhead. You may try to implement some indexes and see how that works for you.

 

[tbg130]

Thanks; I did add a few indexes after figuring out that I had to set up the view with 'schemabinding' (whatever that means) and hacked away at some clustered unique indexes; not really sure if I did it right as I'm a hack, but the time sped up a bit...

Any other suggestions, tutorials, etc. about indexes that are easy to follow would be appreciated...

Thanks again.

 

[unclerico]

http://www.sql-server-performance.com

is a great site for anything performance related

 

[mrdenny]

Are you doing joins, unions, or union alls?

UNION ALL will be the fastest.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[tbg130]

Denny,

UNION ALL is a good tip; but, wondering how that works as it seems to be different than a JOIN in it's functionality.

I have 3 tables with the following:

Table ORGANIZATIONS
OrgID
OrgName

Table ORGIPS
OrgID
IP_LOW
IP_HIGH

Table ORGURLS
OrgID
OrgURL

As the tables all have different fields with the exception of the ORGID, I'm not sure if UNION will work. I need to get one VIEW that shows the ORGID, ORGNAME, IP_LOW, IP_HIGH, and ORGURL in one view, but want it to be faster than it is now!

Thanks for your help and looking forward to another tip for this...

 

[SQLSister]

Try using a stored proc instead of a view. You would join these table on Org_id. You may need left joins depending on if the subordinate tables will definitely have a record or not.

Also look at your indexing. The join field must be indexed in all the tables. It probably is in the one where it is the primary key, but a foreign key relationship does not automatically put an index on the field in the subordinate table.

if you are using the Enterprise edition fo SQL Server you can also index the view which might improve speed.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[mrdenny]

Based on your initial description I thought that you would be able to union the data together. Now that I've seen your table schemas you will need to use joins.

As SQL Sister said, using a stored proc not a view will make things faster. As will checking out your indexing.

How large are your tables?

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[tbg130]

The tables are pretty small; only about 2500 records total (with the goal of getting it to 10,000) in a complete joined view... So we're talking hundreds of records (maybe low thousands) but not 100's of 1000's or millions!

I don't know the first thing about making stored procedures so I guess I've got some work to do!