Query timeout when running large queries in SQL Analyzer

Posted on 2007-10-10
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
    LVL 19

    Accepted Solution

    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

    by:Racim BOUDJAKDJI
    Could you post the view so that we take a look at it?

    Author Comment

    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

    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

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

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    ADCs have gained traction within the last decade, largely due to increased demand for legacy load balancing appliances to handle more advanced application delivery requirements and improve application performance.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
    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…

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now