October 12, 2006 at 9:08 am
Hi All,
I have one single query that has has 17 tables and 16 joins in it.Will retard the speed of the execution?
Regards
Suresh
October 12, 2006 at 9:24 am
Retartd it compared to what???
Depending on the indexing and where conditions, the query can still be very fast.
Can you show it to us?
October 12, 2006 at 9:30 am
SELECT
dh.KNOWN_AS Banker_Name,
ha.TITLE Title,
ha.active_location Location,
org.division_name Sub_Division,
hohh.group_code Group_Name,
hohh.sub_group_code Sub_Group_Name,
CASE WHEN dp.syndicate_number IS NOT NULL THEN 'Y' ELSE 'N' END Syndicate,
CONVERT(varchar(10), dd.calendar_date, 105) Expense_Date,
da.ACCOUNT_LEVEL1_DESC Group1_Name,
da.ACCOUNT_LEVEL2_DESC Group2_Name,
da.ACCOUNT_LEVEL3_DESC Account_Name,
fe.DOLLAR_AMOUNT Trnsaction,
fe.LOCAL_AMOUNT Transaction_Local,
dc.CURRENCY_CODE Transaction_Currency,
fe.LINE_NO Line_Item_Number,
fe.comments Comments,
fe.ATTENDEES Attendees,
decat.description Expense_Category,
fe.STATEMENT_NUMBER Statement_Number,
dts.description Status,
CONVERT(varchar(10), dd1.calendar_date, 105) Status_Date,
dp.Project Project,
dp.Project_Number Project_Number,
dp.Syndicate_Number Syndicate_Number,
dp.Product Product,
dp.Current_Stage Prj_Status,
CONVERT(varchar(10),dp.Status_As_Of,105) Prj_Status_Date,
dd.FISCAL_YEAR_NUMBER Fiscal_Year,
fe.te_source App_Type,
dl.OFFICE_DESC Office,
dl.REGION_DESC Region,
da.ACCOUNT_LEVEL3_CODE Account_Number,
dc1.CURRENCY_CODE Currency,
fe.ID ID,
fe.LEDGER_DEPARTMENT Department,
(select name from ref_company.dbo.company where entity = dp.company_number) Client,
(select name from ref_company.dbo.company where entity = dp.relationship_parent) Relationship_Parent
FROM
dw.dbo.f_tems_expense fe
INNER JOIN dw.dbo.d_gl_account_level3 da
ON fe.OTIS_ACCOUNT_LEVEL3_KEY = da.ACCOUNT_LEVEL3_KEY
AND da.ACCOUNT_LEVEL1_DESC = 'Travel & Entertainment'
INNER JOIN dw.dbo.d_hram_asset dh
ON dh.ibd_id = fe.id
INNER JOIN dw.dbo.d_mis_project dp
ON fe.MIS_PROJECT_KEY = dp.MIS_Project_Key
INNER JOIN dw.dbo.d_day dd
ON fe.EXPENSE_DAY_KEY = dd.DAY_KEY
INNER JOIN dw.dbo.d_day dd1
ON fe.STATUS_DAY_KEY = dd1.DAY_KEY
INNER JOIN dw.dbo.d_tems_processed_location_v dl
ON fe.PROCESSED_OFFICE_KEY = dl.OFFICE_KEY
INNER JOIN dw.dbo.d_tems_expense_category decat
ON fe.TEMS_EXPENSE_CATEGORY_KEY = decat.TEMS_EXPENSE_CATEGORY_KEY
INNER JOIN dw.dbo.d_tems_status dts
ON fe.TEMS_STATUS_KEY = dts.TEMS_STATUS_KEY
INNER JOIN dw.dbo.d_currency dc
ON fe.LOCAL_CURRENCY_KEY = dc.CURRENCY_KEY
INNER JOIN dw.dbo.d_currency dc1
ON fe.REGION_CURRENCY_KEY = dc1.CURRENCY_KEY
INNER JOIN general_reference.dbo.hram_asset ha
ON dh.ibd_id = ha.ibd_id
INNER JOIN general_reference.dbo.hram_org_hierarchy org
ON ha.permanent_group_id = org.element_id
INNER JOIN general_reference.dbo.hram_org_hier_hist hohh
ON ha.permanent_group_id = hohh.element_id
AND dd.calendar_date between hohh.start_date and isnull(hohh.end_date,getdate())
INNER JOIN general_reference.dbo.hram_base_change_history hbch
ON hbch.ibd_id = ha.ibd_id
AND dd.calendar_date between hbch.start_date and isnull(hbch.end_date,getdate())
WHERE 1=1
order by dh.known_as
October 12, 2006 at 9:45 am
Make sure all the columns in the joins are indexed and you should be fine.
How long does this query take to run VS how long you think it should take?
October 12, 2006 at 9:50 am
All the columns being used in the join are key columns and are indexed .This query was run in US and i am not sure how long it has taken but it has brought down the server.
October 12, 2006 at 9:52 am
How much data in each table?
How much data should be returned by the query?
October 13, 2006 at 2:53 am
Hi
When you say brought down the server what exactly do you mean? Crashed it or Used all loads of resources.
Have you had a look at the execution plan on your query?
Mike
October 13, 2006 at 6:41 am
Why are the last 2 columns subselects, instead of 2 more joins? Joining the tables (even outer joins) may be more efficient than subselects.
Depending on the size of the tables involved this query may need some very large temp tables. As a general rule, give SQL Server as much memory as you can (max out the memory on the server machine), and make sure the tempdb is on a drive with enough space for SQL Server to effectively page large temp tables.
Use query analyzer and the "display estimated execution plan" or "show query plan" options to run the query and determine where the bottlenecks are, or where the interrum result sets are very large.
Good luck...
October 13, 2006 at 6:50 am
I would question the practice of giving all the memory to SQL server. In my experience limiting SQL Server so that it does not take all the memory is beneficial as windows also requires some.
October 13, 2006 at 9:11 am
This query is not necessarily bad, but if it is bringing your server down you need to take a long hard look at it. Does the execution plan look optimum? Is it using the indexes you think it should use? Do the rowcount estimates look correct? You may be able to add some indexes, statistics, or join hints that speed it up.
You might want to break the query down into several queries that fill temp tables with intermediate results. Try to choose the combinations which result in the fewest rows & columns. Optimizing these smaller queries may be easier than optimizing the original monster. If doing it in pieces is much faster than doing it in one step, then you know have to abandon the original form.
It's possible you could use the optimized pieces you came up with as derived tables in a new version of the one big query, but you would have to test it to compare performance. How your system handles a big query like this is highly dependent on your hardware, as well as your own tuning skills.
October 16, 2006 at 8:11 am
Did you try to update the appropriate statistics for the columns used in the query.I'm sure that will help your query to be faster by 5 times atleast.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply