July 5, 2012 at 4:16 pm
This is there any way that i can optimize this query.If i run it takes too long.i want to write it in such
way so that it can run faster.
select Ver_day.[DAY]
,'NATIONAL' AS REPORT_LEVEL
,'NATIONAL' AS NODE_ID
,[all_others].[ADDRESS_CLASS]
,sum([UNIQUE_MSISDN])as UNIQUE_MSISDN
,sum([UNIQUE_PDP_ADDRESSES]) as UNIQUE_PDP_ADDRESSES
,avg([AVERAGE_PDP_LIFETIME])as AVERAGE_PDP_LIFETIME
from [dbo].[IPv6_Daily_Reporting] report
inner join
[dbo].[Ip_Ver_day] as Ver_day
on report.[DAY]=ver_day.[day]
inner join
dbo.[IP_Ver_ACCESS_POINT_NAME] as Ver_Apn
on report.ACCESS_POINT_NAME=Ver_Apn.[ACCESS_POINT_NAME]
inner join[dbo].[Ip_ver_Cl_Hset_Tac_OS_PDP] as all_others
on report.ADDRESS_CLASS=all_others.ADDRESS_CLASS
and report.FULL_HANDSET_NAME=all_others.FULL_HANDSET_NAME
and report.TAC_LIST=all_others.TAC_LIST
and report.OS=all_others.OS
and report.PDPAlwaysOn=all_others.PDPAlwaysOn
group by Ver_day.[DAY],[all_others].[ADDRESS_CLASS]
ORDER BY 1, 2, 3
July 5, 2012 at 4:35 pm
While we could make guesses at this from just the code, there's a few pieces of it that we desire to try to help with optimization issues like this.
Please click the link in my sig reading For index/tuning help. It'll walk you through the schema, code, and sqlplan items that we'd need to really help you with this beyond wild guesses.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 5, 2012 at 4:51 pm
please find attached query plan for that.
July 5, 2012 at 5:02 pm
weston_086 (7/5/2012)
please find attached query plan for that.
Please also include the schema and indexing. In particular for ipv6_Daily_reporting, IP_Ver_Access_Point_Name, IP_Ver_Cl_Hset_Tac_OS_PDP. From what I see here these are simply heaps, and that's 98% of your issue. They need indexing.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 5, 2012 at 5:37 pm
These tables have no indexes.so if i have to create index which column would be the best in each table .
July 5, 2012 at 5:44 pm
weston_086 (7/5/2012)
These tables have no indexes.so if i have to create index which column would be the best in each table .
Schema please? I'd rather avoid giving recommendations of using a VARCHAR(MAX) as a clustered index. Honestly, we need all the pieces to help you effectively. Optimization isn't an off the cuff thing.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 5, 2012 at 5:45 pm
Please find attached indexes information for each table.
Thanks
July 5, 2012 at 5:47 pm
weston_086 (7/5/2012)
Please find attached indexes information for each table.Thanks
Yeaaah, not opening an office doc from the web, too many nasty things out there.
Please simply cut and paste the create table code in code="sql" tags here on the forum.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 5, 2012 at 6:10 pm
Here is the create table query for all of them.
CREATE TABLE [dbo].[IPv6_Daily_Reporting](
[DAY] [datetime] NULL,
[NODE_ID] [varchar](50) NULL,
[ADDRESS_CLASS] [varchar](50) NULL,
[ACCESS_POINT_NAME] [varchar](140) NULL,
[FULL_HANDSET_NAME] [varchar](150) NULL,
[TAC_LIST] [varchar](20) NULL,
[UNIQUE_MSISDN] [numeric](18, 0) NULL,
[UNIQUE_PDP_ADDRESSES] [numeric](18, 0) NULL,
[UNIQUE_MSISDN_PDP] [numeric](18, 0) NULL,
[CREATE_PDP] [numeric](18, 0) NULL,
[UPDATE_PDP_IRAT] [numeric](18, 0) NULL,
[UPDATE_PDP_SGSN_RAU] [numeric](18, 0) NULL,
[DELETE_PDP_NORMAL] [numeric](18, 0) NULL,
[DELETE_PDP_ABNORMAL] [numeric](18, 0) NULL,
[TOTAL_MB] [real] NULL,
[AVERAGE_PDP_LIFETIME] [real] NULL,
[OS] [varchar](50) NULL,
[PDPAlwaysOn] [varchar](50) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[IP_Ver_ACCESS_POINT_NAME](
[ACCESS_POINT_NAME] [varchar](140) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Ip_ver_Cl_Hset_Tac_OS_PDP](
[ADDRESS_CLASS] [varchar](50) NULL,
[FULL_HANDSET_NAME] [varchar](150) NULL,
[TAC_LIST] [varchar](20) NULL,
[OS] [varchar](50) NULL,
[PDPAlwaysOn] [varchar](50) NULL
) ON [PRIMARY]
July 5, 2012 at 10:08 pm
Just awesome.. The cost of the query is 1.8 billion and the estimated rows at one point are 417 trillion.
How do you expect this query to run fast?
You do not have a single index on the table. There are no PK ,FK and unique keys are defined.Define those and then your query will surely run faster.
[font="Tahoma"]Try following indexes. These are based on this just one query.Make sure that you decide your indexes based on your overall queries against these tables and not just this one query.[/font]
Table IPv6_Daily_Reporting
Clustered index on following columns
day,address_class columns.
Table Ver_day:
You can have a clustered index on day.I can not see any other column from this tabele used anywhere else so best would be a nonclustered index on the column DAY. Is there any FK conraint from this table to IPv6_Daily_Reporting table?
Table IP_Ver_ACCESS_POINT_NAME
create clustered index on ACCESS_POINT_NAME.I can not see any other column from this tabele used anywhere else so best would be a nonclustered index on the column ACCESS_POINT_NAME .Again, Is there any FK conraint from this table to IPv6_Daily_Reporting table?
Table Ip_ver_Cl_Hset_Tac_OS_PDP
Try to have a clustered index on all the columns in join.
Or again you could have a nonclustered index on all of these columns which would be a covering.The order should be such that most selective column is first column of the index and then the second most selective and so on.
Or you can have a clustered index on one or two columns which could be PK or Unique key and then create another non clustered index on other columns to make it covering.
Or create a non clustered index with most selective columns as part of the key and others as included columns.
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
July 5, 2012 at 11:38 pm
IF IT IS necessary that time you will use order by
ohter wise no need to use because it is take time.
July 10, 2012 at 2:19 pm
Sorry I fell off the planet, came down ill.
Alright, looking this over, there's a few things you can do to help yourself out. Also some data questions.
What is the purpose of IP_Ver_ACCESS_POINT_NAME ? Is this a table that restricts the data in the other ones, or just a list of available names? If it's just a list of names, there's no reason to join to it. You already get that data from another table and there's nothing else in Point_Name you need to get.
Also, might as well clustered index the one column in there, even if it IS VARCHAR(140). If you start adding columns to that table you'll want to adjust it for a surrogate key, 140 bytes is a HUGE clustered index.
This plan you've offered us is an estimated plan. All discussion from here is theoretical until we see an actual.
You're getting row estimates in the 3 billions. This is because of a lack of statistics, most likely. However, there is no WHERE clause on your main query. Expect this to run long anyway. Now, we need to get some indexes (and statistics) into play here.
There's no schema available for Ip_Ver_day. Assuming day is some kind of datetime value, clustered index on that in that table. Keep in mind that a clustered index does not have to be your primary key.
Also, yank the join to the table anyway. You don't use any of the data in IP_Ver_DAY, you just use the DAY value, which you get from IPv6_Daily_Reporting.
What information are you bringing in from IP_Ver_CL_HSET_TAC_OS_PDP? The 5 fields that are in that table are being joined, which means you already have the information, what's the purpose of that join?
Basically, from what I see here, everything you need is right in the Daily Reporting Table. Why are these other tables included?
Now, to speed up your query a bit you could clustered index Daily Reporting on your group by, but you'd need to know how that'll affect the rest of your system. But, in a test environment, I'd start with:
CREATE CLUSTERED INDEX idx_c_IPv6_Daily_Reporting ON IPv6_Daily_Reporting ( Day, AddressClass)
GO
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 10, 2012 at 2:45 pm
I think the query below is equivalent to your original one, w/o all the unnecessary join (unless the join on DAY restricts the rows selected from the "report" table). I assume the join to the "all_others" table is to restrict the rows returned?, that is, verify existence in the other table? Don't see any other need for the JOIN.
The query will run a lot faster with the clus index previously recommended on ([DAY], [address_class]), but that will move the entire table, so be ready for that.
SELECT
report.[DAY]
,'NATIONAL' AS REPORT_LEVEL
,'NATIONAL' AS NODE_ID
,report.[ADDRESS_CLASS]
,SUM(report.[UNIQUE_MSISDN])as UNIQUE_MSISDN
,SUM(report.[UNIQUE_PDP_ADDRESSES]) as UNIQUE_PDP_ADDRESSES
,AVG(report.[AVERAGE_PDP_LIFETIME])as AVERAGE_PDP_LIFETIME
FROM [dbo].[IPv6_Daily_Reporting] report
WHERE
/*EXISTS(
SELECT 1
FROM ...
WHERE ....[DAY] = ....[DAY]
)
AND */
EXISTS (
SELECT 1
FROM [dbo].[Ip_ver_Cl_Hset_Tac_OS_PDP] AS all_others
WHERE
report.ADDRESS_CLASS = all_others.ADDRESS_CLASS
and report.FULL_HANDSET_NAME = all_others.FULL_HANDSET_NAME
and report.TAC_LIST = all_others.TAC_LIST
and report.OS = all_others.OS
and report.PDPAlwaysOn = all_others.PDPAlwaysOn
)
GROUP BY
report.[DAY], report.[ADDRESS_CLASS]
ORDER BY
report.[DAY]
inner join[dbo].[Ip_ver_Cl_Hset_Tac_OS_PDP] as all_others
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply