SQL Query Times out before completion

[wsproperties]

I have a rather large sql query that pulls from 4 different tables with left outer joins. The table also has a grouping due to a total. here is a snipplet of the view (note I did the coding in SQL Analyzer then copied code into a view)

select name,addr1,addr2,city,jobname,sum(giving)as giving,email
from name_table left outer join address_table
on name_id=addr_id
left outer join jobs_table on jobs_id=name_id
left outer join gifts_table on gift_id=name_id
left outer join email_table on phone_id=name_id

group by name,addr1,addr2,city,jobname,email

The reason I group by each and every table is because I get an aggregate function error when I leave out the remaining tables.

Questions: 1. When I run the view I get a timeout message. Is the sql view bombing because I am grouping by several fields. 2. Would this be solved if there was a primary key. I ask because I heard you cannot establish a primary key in a view. Ideally I would only group by one or two table fields at best.

 

[RiverGuy]

What are you using to execute the query? You can always increase the timeout setting if your program supports it.

But really, if your query is too slow, there isn't a stock answer to make it faster. You should read up on indexes in SQL Server, and see if you can get better performance from that. My guess is that you don't have any indexes at the present on the underlying tables, or indexes that don't match your query.

 

[ousoonerjoe]

Make sure your indexes on the tables are in place. Also, when you ran this in QA did it take a while to execute? Did it return exactly what you were looking for without duplicate rows?

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[wsproperties]

I am not an expert in SQL. Can you tell me how to get to my indexes and what to consider when setting the indexes. Also am I right about the grouping? If you get an aggregate error, it means you must include each field in your query. Also this must be updateable. So should I be creating a table or a view?

 

[RiverGuy]

Well, indexes are a concept and there are a lot of options, so you should familiarize yourself with what they are, and what the options are in creating and maintaining them. Here is a good place to start:

http://msdn.microsoft.com/en-us/library/ms188783.aspx

They are created with T-SQL DDL, similar to a CREATE TABLE or CREATE PROCEDURE statement. You can also get to them in SQL Server Management Studio for example, but all that does is write the T-SQL DDL for you and execute it.

You are correct about the grouping, however, there are some other things you can try. For example, if you have a candidate key on your view/table, you can group by just that key and get your aggregate, and then join back a derived table (subquery) to your main table to get the other attributes.

 

[danvlas]

I have a strange feeling that your joins are not correct at all:

Name_id=addr_id

Name_id=gift_id

Name_id=phone_id

That looks like joining the primary keys of the tables.

Are you sure you have the right joins?
If yes (which I seriously doubt), then create non unique indexes on phone_id, gift_id and addr_id and a primary key on name_id. That would speed up the query dramatically.

If not, create the corresponding foreign keys, populate them, re-make the joins and try again...


Daniel Vlas
Systems Consultant

 

[wsproperties]

Then why does it work in query analyzer and not in a view. In addition I am including blank fields that will be updatable. Here is another snipplet:

select nameID,name,addr1,addr2,city,jobname,FY2006,FY2007,email,
space(30) as update_name, space(30) as update_Addr
from name_table left outer join address_table
on nameID=addr_id
left outer join jobs_table on jobs_id=nameID
left outer join gifts_table on gift_id=nameID
left outer join email_table on phone_id=nameID

1.Are you saying because I keep referencing back to nameID, that the view bombs?

2. So for example are you saying I should have said on jobsID=addrID instead of nameID?

3. I did not establish nameID as a primary key. Was that supposed to be done in the view and if so, how?

4. For what i am trying to do should I have done a view or a table. Remember this is supposed to be two fold, it populates existing data and provides blank fields.

5. I thought about breaking this up into two tables,thus putting the blank fields in its own table such as
select space(30) as update_name, space(30) as update_Addr but where would it select from?