• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 275
  • Last Modified:

Query timeout when running large queries in SQL Analyzer

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.

Thanks
Stanton
0
Stanton_Roux
Asked:
Stanton_Roux
  • 2
  • 2
2 Solutions
 
frankyteeCommented:
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.
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Could you post the view so that we take a look at it?
0
 
Stanton_RouxAuthor Commented:
Here is the view

ALTER  VIEW dbo.KHC_OutStandingBalances
AS
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,
                      dbo.Tbl_KHC_Transactions.DISCOUNT
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%'))
0
 
Stanton_RouxAuthor Commented:
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
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
run
set showplan_all on
go
then run (separately) your query, post the results, see if there are any *Table Scans* lines...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now