Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am using SQL Server 2008 SP2 and I'm facing an issue with a particular query.

It is taking 4 sec to 4 min in execution. Please suggest the possibilities.

There is no scope of query optimization. Indexes are also proper.

SELECT ALL X.*
    ,sd1.string_value "sd1_string_value"
    ,sd2.string_value "sd2_string_value"
    ,sd3.string_value "sd3_string_value"
    ,sd4.string_value "sd4_string_value"
    ,sd5.string_value "sd5_string_value"
    ,sd6.string_value "sd6_string_value"
    ,sd7.string_value "sd7_string_value"
    ,wi.r_object_id "wi_r_object_id"
    ,wi.r_act_def_id "wi_r_act_def_id"
    ,wi.r_workflow_id "wi_r_workflow_id"
FROM (
    SELECT *
    FROM (
        SELECT ALL q.content_type q_content_type
            ,q.item_type q_item_type
            ,q.dependency_type q_dependency_type
            ,q.router_id q_router_id
            ,q.event q_event
            ,q.item_name q_item_name
            ,q.task_subject q_task_subject
            ,q.task_name q_task_name
            ,q.NAME q_name
            ,q.sent_by q_sent_by
            ,q.r_object_id q_r_object_id
            ,q.priority q_priority
            ,q.item_id q_item_id
            ,q.date_sent q_date_sent
            ,q.task_state q_task_state
            ,q.due_date q_due_date
            ,q.task_number q_task_number
            ,q.source_docbase q_source_docbase
            ,q.source_event q_source_event
            ,wf.process_id wf_process_id
            ,wf.r_object_id wf_r_object_id
            ,CAST(ROW_NUMBER() OVER (
                    ORDER BY q.date_sent DESC
                        ,q.r_object_id ASC
                    ) AS INT) AS dm_rnum
        FROM dm_workflow_sp wf
            ,dmi_queue_item_sp q
        WHERE (
                ((q.router_id = wf.r_object_id))
                AND (
                    q.NAME IN (
                        N'Hemavathy Aneesh. Nair'
                        ,N'al_nko1000abs_group'
                        ,N'al_nmp1000abs_group'
                        ,N'al_nmu1000abs_group'
                        ,N'al_nub1000abs_group'
                        ,N'al_nue1000abs_group'
                        ,N'edms_user'
                        )
                    AND (wf.process_id = N'4b027d9a800077e8')
                    AND (
                        (q.delete_flag = 0)
                        AND q.dependency_type IN (
                            N'0'
                            ,N'1'
                            ,N'2'
                            ,N'3'
                            ,N'4'
                            ,N'5'
                            ,N'6'
                            )
                        )
                    )
                )
        ) dm_inline_view_1
    WHERE dm_rnum >= 1
        AND dm_rnum <= 101
    ) X
LEFT JOIN (
    SELECT ALL dmc_wfsd_element_string.workflow_id
        ,dmc_wfsd_element_string.object_name
        ,dmc_wfsd_element_string.r_object_id
        ,dmc_wfsd_element_string.process_id
        ,dmc_wfsd_element_string.string_value
    FROM dmc_wfsd_element_string_sp dmc_wfsd_element_string
    WHERE ((dmc_wfsd_element_string.object_name = N'dm_taskName'))
        AND (
            dmc_wfsd_element_string.i_has_folder = 1
            AND dmc_wfsd_element_string.i_is_deleted = 0
            )
    ) sd1 ON (sd1.workflow_id = X.q_router_id)
LEFT JOIN (
    SELECT ALL dmc_wfsd_element_string.workflow_id
        ,dmc_wfsd_element_string.object_name
        ,dmc_wfsd_element_string.r_object_id
        ,dmc_wfsd_element_string.process_id
        ,dmc_wfsd_element_string.string_value
    FROM dmc_wfsd_element_string_sp dmc_wfsd_element_string
    WHERE ((dmc_wfsd_element_string.object_name = N'circel'))
        AND (
            dmc_wfsd_element_string.i_has_folder = 1
            AND dmc_wfsd_element_string.i_is_deleted = 0
            )
    ) sd2 ON (sd2.workflow_id = X.q_router_id)
LEFT JOIN (
    SELECT ALL dmc_wfsd_element_string.workflow_id
        ,dmc_wfsd_element_string.object_name
        ,dmc_wfsd_element_string.r_object_id
        ,dmc_wfsd_element_string.process_id
        ,dmc_wfsd_element_string.string_value
    FROM dmc_wfsd_element_string_sp dmc_wfsd_element_string
    WHERE ((dmc_wfsd_element_string.object_name = N'VendorCode'))
        AND (
            dmc_wfsd_element_string.i_has_folder = 1
            AND dmc_wfsd_element_string.i_is_deleted = 0
            )
    ) sd3 ON (sd3.workflow_id = X.q_router_id)
LEFT JOIN (
    SELECT ALL dmc_wfsd_element_string.workflow_id
        ,dmc_wfsd_element_string.object_name
        ,dmc_wfsd_element_string.r_object_id
        ,dmc_wfsd_element_string.process_id
        ,dmc_wfsd_element_string.string_value
    FROM dmc_wfsd_element_string_sp dmc_wfsd_element_string
    WHERE ((dmc_wfsd_element_string.object_name = N'VendorName'))
        AND (
            dmc_wfsd_element_string.i_has_folder = 1
            AND dmc_wfsd_element_string.i_is_deleted = 0
            )
    ) sd4 ON (sd4.workflow_id = X.q_router_id)
LEFT JOIN (
    SELECT ALL dmc_wfsd_element_string.workflow_id
        ,dmc_wfsd_element_string.object_name
        ,dmc_wfsd_element_string.r_object_id
        ,dmc_wfsd_element_string.process_id
        ,dmc_wfsd_element_string.string_value
    FROM dmc_wfsd_element_string_sp dmc_wfsd_element_string
    WHERE ((dmc_wfsd_element_string.object_name = N'InvoiceNumber'))
        AND (
            dmc_wfsd_element_string.i_has_folder = 1
            AND dmc_wfsd_element_string.i_is_deleted = 0
            )
    ) sd5 ON (sd5.workflow_id = X.q_router_id)
LEFT JOIN (
    SELECT ALL dmc_wfsd_element_string.workflow_id
        ,dmc_wfsd_element_string.object_name
        ,dmc_wfsd_element_string.r_object_id
        ,dmc_wfsd_element_string.process_id
        ,dmc_wfsd_element_string.string_value
    FROM dmc_wfsd_element_string_sp dmc_wfsd_element_string
    WHERE ((dmc_wfsd_element_string.object_name = N'InvoiceAmountCommaFormat'))
        AND (
            dmc_wfsd_element_string.i_has_folder = 1
            AND dmc_wfsd_element_string.i_is_deleted = 0
            )
    ) sd6 ON (sd6.workflow_id = X.q_router_id)
LEFT JOIN (
    SELECT ALL dmc_wfsd_element_string.workflow_id
        ,dmc_wfsd_element_string.object_name
        ,dmc_wfsd_element_string.r_object_id
        ,dmc_wfsd_element_string.process_id
        ,dmc_wfsd_element_string.string_value
    FROM dmc_wfsd_element_string_sp dmc_wfsd_element_string
    WHERE ((dmc_wfsd_element_string.object_name = N'Parking_Doc_Id'))
        AND (
            dmc_wfsd_element_string.i_has_folder = 1
            AND dmc_wfsd_element_string.i_is_deleted = 0
            )
    ) sd7 ON (sd7.workflow_id = X.q_router_id)
LEFT JOIN dmi_workitem_sp wi ON (wi.r_queue_item_id = X.q_r_object_id)
ORDER BY X.q_date_sent DESC
    ,X.q_r_object_id ASC
share|improve this question
1  
You have many contructs in your query with can cause the slow execution like: sub selects, left outer joins, select * etc. Looking at the query in the current formattion will not lead to any optimalizations, try to reformat your query in a more readable format and tell us wat you are doing (functional). –  peer Mar 20 '13 at 10:52
    
You are going to need to format that much more usefully if you want anyone to consider looking at it in depth. Look at pre / code block formatting in stackoverflow.com/editing-help, separate and indent the code in a readable manner. –  David Spillett Mar 20 '13 at 10:53
    
Hi Thanks for your response. Basically I am working on a product Documentum Taskspace. This query is used to populate user inbox. as we are using product we cannot modify query we don't have code for this. The reason why I have posted my query in this forum is that the same query is taking 4 sec to 4 min in execution. So I don't think this is a problem with query. Please suggest what else I can check. –  Vikas Gupta Mar 20 '13 at 10:55
    
Regarding "there is no scope of query optimization" looking at the stuff I can see at first glance (several co-related subqueries as join targets and so forth) I very much doubt that. Regarding "indexes are also proper": show us the keys and indexes that are defined, perhaps there is something that has been missed someone here can help you by suggesting. –  David Spillett Mar 20 '13 at 10:58
    
Thanks David, I want to share query plan here. Can you please suggest how I can attach a file? –  Vikas Gupta Mar 20 '13 at 11:00

1 Answer 1

The query taking a lot longer to run some times could be due to I/O or memory issues. If it is table (or index) scanning over a large amount of data and that data is sometimes in memory and sometimes has been pushed out by other data, then that could explain the speed difference of seconds to minutes: when the data is in RAM those scans will be CPU bound, when it is not it'll be IO bound. If that is the case than throwing more memory at the database server might well help, though removing the need to scan large amounts of index/heap by query/index optimisation will help more (especially as the data grows over time).

Another possibility is that high activity on the server at times could also explain the difference, either due to physical resource contention (CPU or IO) or locking issues (another long running query holding locks on data this query needs).

share|improve this answer
    
Hi David,<BR> Just to add @night a pre-configured weekly maintenance schedule executed. After this when we saw Today query is taking 4-5 seconds. In maintenance we are rebuilding indexes. Please suggest if we can execute this maintenance plan frequently? Or may be there are chances of something wrong in indexs? –  Vikas Gupta Mar 21 '13 at 13:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.