March 13, 2018 at 4:10 am
Hello,
Personally I feel bad about to use subselects in queries, it the condition is only one simple entry. But I also have no idea to avoid it and to optimize the execution time. Can you help me?
SELECT *
FROM tableA
WHERE program_id = 400
AND rmp_vkey = (SELECT MAX(rmp_vkey) vkey FROM tableA WHERE program_id = 400)
ORDER BY rmp_week
The table structure:
CREATE TABLE [dbo].tableA(
[program_id] [int] NOT NULL,
[rmp_version] [datetime] NULL,
[vkey] [int] NOT NULL,
[rmp_week] [int] NOT NULL,
[rmp_exp_qty] [int] NULL,
[comment] [varchar](511) NULL,
PRIMARY KEY CLUSTERED
(
[program_id] ASC,
[rmp_vkey] ASC,
[rmp_week] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
March 13, 2018 at 5:19 am
Apart from splitting it into two queries, I can't see any way of simplifying what you have written:
DECLARE @max_rmp_vkey INT = (
SELECT MAX(rmp_vkey)
FROM monitor_rampup_curve
WHERE program_id = 400
);
SELECT *
FROM tableA
WHERE
program_id = 400
AND rmp_vkey = @max_rmp_vkey
ORDER BY rmp_week;
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 13, 2018 at 5:28 am
adiedler - Tuesday, March 13, 2018 4:10 AMHello,
Personally I feel bad about to use subselects in queries
Why?
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
March 13, 2018 at 11:24 am
TableA is not the issue. And the subquery is not correlated, so the subquery is not directly the issue either.
Look at the query plan. If SQL's having to do too much I/O on monitor_rampup_curve, then you may need an index on:
( program_id, rmp_vkey )
Depending on the most common query pattern, it could be best to cluster that table on that, the same as for TableA.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 13, 2018 at 12:01 pm
adiedler - Tuesday, March 13, 2018 4:10 AMHello,
Personally I feel bad about to use subselects in queries, it the condition is only one simple entry. But I also have no idea to avoid it and to optimize the execution time. Can you help me?
SELECT *
FROM tableA
WHERE program_id = 400
AND rmp_vkey = (SELECT MAX(rmp_vkey) vkey FROM monitor_rampup_curve WHERE program_id = 400)
ORDER BY rmp_weekThe table structure:
CREATE TABLE [dbo].tableA(
[program_id] [int] NOT NULL,
[rmp_version] [datetime] NULL,
[vkey] [int] NOT NULL,
[rmp_week] [int] NOT NULL,
[rmp_exp_qty] [int] NULL,
[comment] [varchar](511) NULL,
PRIMARY KEY CLUSTERED
(
[program_id] ASC,
[rmp_vkey] ASC,
[rmp_week] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
I would write the query as a correlated subquery so that if I needed to change program_id was querying I would only have to change it in one place:
SELECT *
FROM tableA a
WHERE a.program_id = 400
AND rmp_vkey = (SELECT MAX(mrc.rmp_vkey) vkey
FROM monitor_rampup_curve mrc
WHERE mrc.program_id = a.program_id)
ORDER BY a.rmp_week;
March 13, 2018 at 12:10 pm
Sorry guys, there is an error in the SQL, the right used SQL looks like this. The target is only to have all entries from table with the latest / highest value from rmp_vkey
SELECT *
FROM tableA
WHERE program_id = 400
AND rmp_vkey = (SELECT MAX(rmp_vkey) vkey FROM tableA WHERE program_id = 400)
ORDER BY rmp_week
The subselect is from the same table, could it be easier to make the where condition?
March 13, 2018 at 12:13 pm
adiedler - Tuesday, March 13, 2018 12:10 PMSorry guys, there is an error in the SQL, the right used SQL looks like this. The target is only to have all entries from table with the latest / highest value from rmp_vkey
SELECT *
FROM tableA
WHERE program_id = 400
AND rmp_vkey = (SELECT MAX(rmp_vkey) vkey FROM tableA WHERE program_id = 400)
ORDER BY rmp_week
The subselect is from the same table, could it be easier to make the where condition?
Then my simplified rewrite would be:
SELECT *
FROM tableA a
WHERE a.program_id = 400
AND rmp_vkey = (SELECT MAX(a1.rmp_vkey) vkey
FROM tableA a1
WHERE a1.program_id = a.program_id)
ORDER BY a.rmp_week;
March 14, 2018 at 11:37 am
adiedler - Tuesday, March 13, 2018 12:10 PMSorry guys, there is an error in the SQL, the right used SQL looks like this. The target is only to have all entries from table with the latest / highest value from rmp_vkey
SELECT *
FROM tableA
WHERE program_id = 400
AND rmp_vkey = (SELECT MAX(rmp_vkey) vkey FROM tableA WHERE program_id = 400)
ORDER BY rmp_week
The subselect is from the same table, could it be easier to make the where condition?
I don't think that you want to do a subselect at all. You should be using a CTE.
;
WITH CTE AS
(
SELECT *, DENSE_RANK() OVER(PARTITION BY program_id ORDER BY rmp_vkey DESC) AS dr
FROM TableA
)
SELECT *
FROM CTE
WHERE program_id = 400
AND dr = 1
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply