Query timeout when running large queries in SQL Analyzer

Posted on 2007-10-10
Medium Priority
Last Modified: 2010-04-18
HI There
I have created a view pulls data from two fairly large tables.
The view represents alot of data.
I have created the relationships and indexes between the two tables.
When I run the view or try query the view from SQL Analyzer I keep getting a TIMEOUT ERROR.
Even if I return top 10
I ran the Perfmon while I was running the query and noticed that the Avg Disk Queue length
and the % processor time was peaking.

Is there a way to optimize SQL so that I am able to get large amounts of databack without it timing out.Even if it takes a while to run.

Question by:Stanton_Roux
  • 2
  • 2
LVL 19

Accepted Solution

frankytee earned 1000 total points
ID: 20047434
do you have a regular db maintenance plan to regularly rebuild the indexes and update statistics?
also set the "show execution plan" when you run your query so you can see the bottlenecks
there are some configurations to reduce sql overheads like
set nocount on
which will turn off the recordcount for each query run
for the two large tables, what is the clustered index for the "child" table? most dba default the clustered index to the PK but i find generally it would be better to have the clustered on the FK (it would depend on app to app) for the child table.
LVL 23

Assisted Solution

Racim BOUDJAKDJI earned 1000 total points
ID: 20047460
Could you post the view so that we take a look at it?

Author Comment

ID: 20047531
Here is the view

ALTER  VIEW dbo.KHC_OutStandingBalances
SELECT     dbo.Tbl_KHC_Visits.FOLDERNO AS [Folder No], dbo.Tbl_KHC_Visits.EPISODENO AS Episode, dbo.Tbl_KHC_Visits.VISITNO AS Visit,
                      dbo.Tbl_KHC_Visits.MEDAIDTYPE AS Type, dbo.Tbl_KHC_Visits.MEDAIDADM, dbo.Tbl_KHC_Visits.MEDAID AS [Medical Aid],
                      dbo.Tbl_KHC_Visits.HOSPNAME, dbo.Tbl_KHC_Visits.HOSPCODE, dbo.Tbl_KHC_Visits.VISITTYPE, dbo.Tbl_KHC_Visits.ADMITDATE AS [Admit Date],
                      dbo.Tbl_KHC_Visits.DISCHARGE AS [Discharge Date], dbo.Tbl_KHC_Visits.WARDBED, dbo.Tbl_KHC_Visits.LOCATION,
                      dbo.Tbl_KHC_Visits.ROOMTYPE AS Roomtype, dbo.Tbl_KHC_Visits.ACCOMRATE, dbo.Tbl_KHC_Visits.RATE,
                      dbo.Tbl_KHC_Visits.STATUS AS [Account Status], dbo.Tbl_KHC_Transactions.TRANSDATE AS [Transaction Date],
                      dbo.Tbl_KHC_Transactions.TXTYPE AS [Transaction Type], dbo.Tbl_KHC_Transactions.TARIFF, DATEDIFF([day], dbo.Tbl_KHC_Visits.DISCHARGE,
                      GETDATE()) AS [Days Outstanding], SUM(dbo.Tbl_KHC_Transactions.MEDPORTION + dbo.Tbl_KHC_Transactions.PATPORTION) AS Amount,
FROM         dbo.Tbl_KHC_Visits INNER JOIN
                      dbo.Tbl_KHC_Transactions ON dbo.Tbl_KHC_Visits.FOLDERNO = dbo.Tbl_KHC_Transactions.FOLDERNO AND
                      dbo.Tbl_KHC_Visits.EPISODENO = dbo.Tbl_KHC_Transactions.EPISODENO AND
                      dbo.Tbl_KHC_Visits.VISITNO = dbo.Tbl_KHC_Transactions.VISITNO
GROUP BY dbo.Tbl_KHC_Visits.FOLDERNO, dbo.Tbl_KHC_Visits.EPISODENO, dbo.Tbl_KHC_Visits.VISITNO, dbo.Tbl_KHC_Visits.MEDAIDTYPE,
                      dbo.Tbl_KHC_Visits.MEDAIDADM, dbo.Tbl_KHC_Visits.MEDAID, dbo.Tbl_KHC_Visits.HOSPNAME, dbo.Tbl_KHC_Visits.HOSPCODE,
                      dbo.Tbl_KHC_Visits.VISITTYPE, dbo.Tbl_KHC_Visits.ADMITDATE, dbo.Tbl_KHC_Visits.DISCHARGE, dbo.Tbl_KHC_Visits.WARDBED,
                      dbo.Tbl_KHC_Visits.LOCATION, dbo.Tbl_KHC_Visits.ROOMTYPE, dbo.Tbl_KHC_Visits.ACCOMRATE, dbo.Tbl_KHC_Visits.RATE,
                      dbo.Tbl_KHC_Visits.STATUS, dbo.Tbl_KHC_Transactions.TRANSDATE, dbo.Tbl_KHC_Transactions.TXTYPE, dbo.Tbl_KHC_Transactions.TARIFF,
                      DATEDIFF([day], dbo.Tbl_KHC_Visits.DISCHARGE, GETDATE()), dbo.Tbl_KHC_Transactions.DISCOUNT
HAVING      (SUM(dbo.Tbl_KHC_Transactions.MEDPORTION + dbo.Tbl_KHC_Transactions.PATPORTION) > 0) AND (NOT (dbo.Tbl_KHC_Visits.MEDAID LIKE 'H1%'))

Author Comment

ID: 20047551
In The execution plan the sorting has the most cost.

I have created indesxes on the FolderNo ,EpisodeNo ,VisitNo.
Those are also the Primary keys on the Visits Table.
I have a relationship set on those fields aswell
LVL 23

Expert Comment

ID: 20047618
set showplan_all on
then run (separately) your query, post the results, see if there are any *Table Scans* lines...

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article provides a convenient collection of links to Microsoft provided Security Patches for operating systems that have reached their End of Life support cycle. Included operating systems covered by this article are Windows XP,  Windows Server…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question