April 16, 2015 at 11:20 pm
Original QUery to optimize:
SELECT FC.cexrate,
FC.cagrate,
FC. forex_tag_no,
FC.parent_agent_cd AS CollectingAgentCd,
FC.paying_agent_cd AS PayingAgentCd
FROM (SELECT
c.ex_rate AS cExRate,
c.agent_rate AS cAgRate,
c.forex_tag_no,
c.parent_agent_cd,
c.paying_agent_cd,
Row_number()
OVER(
partition BY c.forex_tag_no, c.paying_agent_cd
ORDER BY c.created_on DESC) AS Row
FROM dbo.forex_coll c
WHERE c.parent_agent_cd = 'MY0001'
) FC
WHERE FC.row = 1
Could you please suggest above query for optimization and suggest what and how index could be used to optimize above query?
April 17, 2015 at 2:24 am
The two queries are different: the first one returns the first row (by created_on) for each group on (c.forex_tag_no ,c.paying_agent_cd).
The second query returns one row based on no order.
Stick to the first syntax.
-- Gianluca Sartori
April 17, 2015 at 7:48 am
Thanks for reply. Reply could you please suggest on changed query for optimization.
April 17, 2015 at 7:57 am
An index on (parent_agent_cd , forex_tag_no, paying_agent_cd , created_on ) might help.
Can you show us the actual plan?
-- Gianluca Sartori
April 17, 2015 at 8:07 am
Table definitions, index definitions and execution plan please?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 17, 2015 at 8:55 am
This table has 40 Lakhs records. It is taking currently 7 seconds. I need to bring its time to 1-2 seconds. Is it possible?
I have created index based on actual execution plan.
-------------------------------------------------------------------------------------------
create nonclustered index idx_combo1_forex_coll on forex_coll(parent_agent_cd)
include(ex_rate,created_on,forex_tag_no,agent_rate,paying_agent_cd);
-----------------------------------------------------------------------------------------
Table Defination:
CREATE TABLE [dbo].[forex_coll](
[parent_agent_cd] [varchar](25) NOT NULL,
[forex_date] [date] NOT NULL,
[rate_code] [varchar](5) NOT NULL,
[from_crncy_cd] [varchar](5) NOT NULL,
[to_crncy_cd] [varchar](5) NOT NULL,
[rate_srl_num] [int] NULL,
[crncy_unit] [numeric](10, 4) NULL,
[ex_rate] [numeric](10, 4) NULL,
[created_on] [datetime] NULL,
[modified_on] [datetime] NULL,
[cre_parent_agent_cd] [varchar](25) NULL,
[created_by] [varchar](30) NULL,
[mod_parent_agent_cd] [varchar](25) NULL,
[modified_by] [varchar](30) NULL,
[forex_tag_no] [numeric](18, 0) NOT NULL,
[agent_rate] [numeric](10, 4) NOT NULL,
[paying_agent_cd] [varchar](25) NULL,
[auto_id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
CONSTRAINT [PK_Forex_Coll] PRIMARY KEY CLUSTERED
(
[auto_id] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
April 17, 2015 at 8:56 am
Not a picture of the execution plan. The saved execution plan. The picture is missing all the important information.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 17, 2015 at 9:07 am
I have saved the execution plan Sir. I have executed in small database. This query has execution time as 8 seconds.
April 17, 2015 at 9:17 am
It runs for 8 seconds in the database where you captured that execution plan?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 17, 2015 at 9:35 am
Sir, I captured the execution plan in small data set where it executes in 6 seconds.
April 17, 2015 at 10:10 am
Could you please post the execution plan from the database which has the poor performance? It really doesn't help to see how SQL runs the query against a small data set when that's not how it normally runs.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 17, 2015 at 10:28 am
Dear Sir, I have kept execution from production.
April 17, 2015 at 3:02 pm
The optimal index for this query is the following:
CREATE NONCLUSTERED INDEX IX_ForThisQuery
ON forex_coll (
parent_agent_cd ASC,
forex_tag_no ASC,
paying_agent_cd ASC,
created_on DESC
)
INCLUDE (
agent_rate,
ex_rate
)
Whether such a big index will hurt write performance, only you can tell. Run extensive tests against the actual workload and apply the index only if acceptable.
Hope this helps
Gianluca
-- Gianluca Sartori
April 17, 2015 at 6:00 pm
Dear Sir,
We have 2 Database one is R/W(read/Write) and another is Read only database. And R/W database server replicates data in R only database server. Given issue is in Read only server as that query will be executed on Read only DB server. Okay drop previous my index and apply this index in production. One another Question Sir,
If i make big index like this in Read only server, does it make replication slow?
Thanks Sir In advance.
April 18, 2015 at 7:50 am
keshab.basnet (4/17/2015)
Dear Sir,We have 2 Database one is R/W(read/Write) and another is Read only database. And R/W database server replicates data in R only database server. Given issue is in Read only server as that query will be executed on Read only DB server. Okay drop previous my index and apply this index in production. One another Question Sir,
If i make big index like this in Read only server, does it make replication slow?
Thanks Sir In advance.
There is a risk that it will make replication slow, but only by testing will you be able to tell whether the effect is large enough to matter.
Tom
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply