Lenthgy Execution Time-Newbie

[btacy]

Hi,
i wrote out a query for a view and it takes too long to execute (abt 10 mins). this view will need to be refreshed at various times, so i need it to execute faster.
Any advice on what to do?
thanks

 

[gmmastros]

How can we possibly give you any advice? You didn't show us the view. Generically, I would recommend a google search for 'query performance'. If you want to show the query, maybe we can help a little more.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLSister]

Without seeing the code, the best I can offer is that you should see if your indexing is correct.

"NOTHING is more important in a database than integrity." ESquared

 

[btacy]

i'm sorry about that guys,
i should also add that when i try to execute it in a view (open the view), it times out, meanwhile executing the query alone succeeds, but takes a long time.
Here's the the code
thanks in advance


SELECT LOOKUP_2.LOOKUP_SHORT_DESC AS Delegate_Type, LOOKUP_1.LOOKUP_SHORT_DESC AS Delegate_Status,
dbo.DELEGATE.NAME, dbo.EMAIL.EMAIL_ADDRESS, dbo.DELEGATE.DELEGATE_REF, dbo.DELEGATE.INDIVIDUAL_REF,
dbo.DELEGATE.CODE AS Delegate_Code, dbo.DELEGATE.STATUS, dbo.DELEGATE.BADGE_TITLE, dbo.DELEGATE.TOTAL_AMOUNT,
LOOKUP_5.LOOKUP_FULL_DESC AS Payment_Method, dbo.DELEGATE.NO_INSTALMENTS, dbo.LOCATION.COUNTRY,
dbo.LOOKUP.LOOKUP_FULL_DESC AS Question, dbo.ANSWER.DESCRIPTION AS Answer, dbo.EVENT.START_DATE AS Event_Start_Date,
dbo.EVENT.END_DATE AS Event_End_Date, dbo.DELEGATE.CREATE_TIMESTAMP AS Delegate_Creat_Timestamp,
dbo.DELEGATE.COMMENT AS Delegate_COmment, dbo.DELEGATE_QUESTION.comments AS Delegate_Question_Comments, dbo.EVENT.EVENT_REF,
dbo.EVENT.CODE AS Event_Code, dbo.EVENT.NAME AS Event, dbo.EVENT.CATEGORY AS Category_Code,
LOOKUP_3.LOOKUP_FULL_DESC AS Event_Category, dbo.EVENT.NL_CODE, dbo.EVENT.DEPTNO, dbo.EVENT.CCNO, dbo.EVENT.VENUE_REF,
dbo.EVENT.CURRENCY, dbo.EVENT.allow_bookings, dbo.EVENT.status AS Event_Status, dbo.EVENT_RATE.EVENT_RATE_REF,
dbo.EVENT_RATE.DESCRIPTION, dbo.EVENT_RATE.NET_VALUE, dbo.EVENT_RATE.DEFAULT_RATE, dbo.EVENT_RATE.CLASS,
dbo.EVENT_RATE.GRADE, dbo.SESSION.SESSION_REF, dbo.SESSION.CODE AS Session_Code, dbo.SESSION.NAME AS Session_Name,
dbo.SESSION.START_DATE AS Session_Start_Date, dbo.SESSION.END_DATE AS Session_End_Date,
dbo.SESSION.START_TIME AS Session_Start_Time, dbo.SESSION.END_TIME AS Session_End_Time,
LOOKUP_4.LOOKUP_FULL_DESC AS Session_Category, dbo.SESSION.VENUE_REF AS Session_Venue_Ref, dbo.SESSION.ROOM AS Session_Room,
dbo.SESSION.allow_bookings AS Session_Allow_Bookings, dbo.DELEGATE_SESSION.NO_DELEGATES, dbo.DELEGATE_SESSION.SEAT_FROM,
dbo.DELEGATE_SESSION.SEAT_TO, dbo.DELEGATE_SESSION.FEEPAYING_MEMBER, dbo.INDIVIDUAL.EXTERNAL_REF,
dbo.INDIVIDUAL.FORENAMES, dbo.INDIVIDUAL.INITIALS, dbo.INDIVIDUAL.SURNAME, dbo.INDIVIDUAL.TITLE, dbo.INDIVIDUAL.SALUTATION,
dbo.INDIVIDUAL.LABEL_NAME, dbo.INDIVIDUAL.DATE_OF_BIRTH, dbo.INDIVIDUAL.GENDER, dbPACIF.AMOUNT, dbPACIF.PAID_STATUS
FROM dbo.EVENT_RATE INNER JOIN
dbo.LOOKUP AS LOOKUP_3 INNER JOIN
dbo.EVENT INNER JOIN
dbo.DELEGATE ON dbo.EVENT.EVENT_REF = dbo.DELEGATE.EVENT_REF INNER JOIN
dbo.DELEGATE_SESSION ON dbo.EVENT.EVENT_REF = dbo.DELEGATE_SESSION.EVENT_REF AND
dbo.DELEGATE.DELEGATE_REF = dbo.DELEGATE_SESSION.DELEGATE_REF INNER JOIN
dbo.DELEGATE_QUESTION ON dbo.DELEGATE.DELEGATE_REF = dbo.DELEGATE_QUESTION.DELEGATE_REF INNER JOIN
dbo.ANSWER ON dbo.DELEGATE_QUESTION.ANSWER_REF = dbo.ANSWER.ANSWER_REF INNER JOIN
dbo.LOOKUP ON dbo.ANSWER.QUESTION = dbo.LOOKUP.LOOKUP_REF INNER JOIN
dbo.INDIVIDUAL_LOC ON dbo.DELEGATE.INDIVIDUAL_REF = dbo.INDIVIDUAL_LOC.INDIVIDUAL_REF INNER JOIN
dbo.EMAIL ON dbo.DELEGATE.INDIVIDUAL_REF = dbo.EMAIL.INDIVIDUAL_REF ON LOOKUP_3.LOOKUP_REF = dbo.EVENT.CATEGORY ON
dbo.EVENT_RATE.EVENT_RATE_REF = dbo.DELEGATE_SESSION.EVENT_RATE_REF INNER JOIN
dbo.LOOKUP AS LOOKUP_5 ON dbo.DELEGATE.PAY_METHOD = LOOKUP_5.LOOKUP_REF INNER JOIN
dbo.INDIVIDUAL ON dbo.DELEGATE.INDIVIDUAL_REF = dbo.INDIVIDUAL.INDIVIDUAL_REF INNER JOIN
dbo.LOOKUP AS LOOKUP_4 INNER JOIN
dbo.SESSION ON LOOKUP_4.LOOKUP_REF = dbo.SESSION.CATEGORY ON dbo.EVENT.EVENT_REF = dbo.SESSION.EVENT_REF LEFT OUTER JOIN
dbo.LOCATION ON dbo.INDIVIDUAL_LOC.LOCATION_REF = dbo.LOCATION.LOCATION_REF LEFT OUTER JOIN
dbo.LOOKUP AS LOOKUP_2 ON dbo.DELEGATE.TYPE = LOOKUP_2.LOOKUP_REF LEFT OUTER JOIN
dbo.LOOKUP AS LOOKUP_1 ON dbo.DELEGATE.STATUS = LOOKUP_1.LOOKUP_REF LEFT OUTER JOIN
dbPACIF ON dbo.DELEGATE_SESSION.DELEGATE_SESS_REF = dbPACIF.SOURCE_REF
WHERE (dbPACIF.TRANS_TYPE = 'EV' OR
dbPACIF.TRANS_TYPE IS NULL) AND (dbo.EMAIL.MAIN_EMAIL = 'Y') AND (dbo.INDIVIDUAL_LOC.MAIN_LOCATION = 'Y')
ORDER BY dbo.DELEGATE.NAME, dbo.DELEGATE_QUESTION.QUESTION

 

[SQLSister]

First clean up those joins, they don't even make sense. Put the join on one line and the on conditions relating to that join immediately following it not mixed all through the code as you have it. Heck you might have some uncessary conditions there but it's so mixed up I couldn't figure it out to be sure.

Next why are you joining to lookup 5 separate times? You didn't put all your lookup table values in one table did you? This is generally a very poor idea. Very much a pain to maintain and can cause locking issues because every single query will call that table and often multiple times.

What does your execution plan say if you run this from query analyzer? Is is using indexes or doing table scans?

Can you live with dirty reads? Nolock hints might help this.

"NOTHING is more important in a database than integrity." ESquared

 

[btacy]

thanks SQLSister,
i inherited the DB and all the lookup table values are in one table, that's why i had to join the table at different times. the execution plan is doing table scans. What do you mean by dirty reads? i'm quite new to SQL Server, so i'd appreciate every help.

 

[SQLSister]

Table scans means it isn't using indexes effectively. First what indexes do you have on these tables?

To find out about dirty reads, go to books online (BOL) and look up Set Transaction Isolation level and read the decription under the Read uncommitted section. If you use
Set Tranasction Isilation Level Read Uncommitted or use the (nolock) hint in your query, you will get dirty reads but usually the query will return faster. You will also have less blocking in your system. To get a better understanding of this issue read about locking in BOL.

"NOTHING is more important in a database than integrity." ESquared

 

[btacy]

Thanks again, i checked the indexes on the tables and created some others, based on the advise given by the query analyzer. I also cleaned up a number of the joins as you advised. Presently, it's running at about at about 70% faster.
Cheers.