Neea a better performance query
-
hi pros,
hope every thing goes well,
SET STATISTICS IO ON
SET STATISTICS TIME ON
DECLARE @Tbl AS TABLE (
id INT IDENTITY (1, 1), Mainid INT, Subid INT, NAME VARCHAR (100)
)
INSERT INTO @Tbl
(
Mainid, Subid, NAME
)
SELECT c.[object_id], c.column_id, c.name
FROM sys.[columns] c
ORDER BY
c.[object_id], c.column_id
DECLARE @id INT = 10
SELECT *
FROM @Tbl t
WHERE EXISTS(
SELECT 1
FROM @Tbl t2
WHERE t.id < t2.id
AND t.Mainid = t2.Mainid
AND EXISTS(
SELECT 1
FROM @Tbl t3
WHERE t3.id = @id
AND t2.Mainid = t3.Mainid
)
)
SELECT *
FROM @Tbl t
WHERE t.id NOT IN (SELECT MAX (t2.id)
FROM @Tbl t2
INNER JOIN @Tbl t3
ON t3.Mainid = t2.Mainid
WHERE t.Mainid = t2.Mainid
AND t3.id = @id)
well my requirement is when i gave a id of a record, i want to get all records of that record's Mainid group except the last record of that group
let's say
idMidSidNAME
131rsid
232rscolid
333hbcolid
434rcmodified
when i gave 1 or 2 or 3 or 4 the expected result would be
idMidSidNAME
131rsid
232rscolid
333hbcolid
which one is the best way,
when i use first query scan count is small but time is little big higher than the second query
when i use the second query scan count is large but the time is small
if you have any other idea or query please suggest me to the right way
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url] -
Hi,
Having had a brief look, the second query (on my PC) was better in terms of CPU and fewer reads.
I also added a PK to the table variable which further improved your example queries:
DECLARE @Tbl AS TABLE(
id INT IDENTITY(1, 1)
PRIMARY KEY
, Mainid INT
, Subid INT
, NAME VARCHAR(100)
)
INSERT INTO @Tbl
(
Mainid
, Subid
, NAME
)
SELECT
c.[object_id]
, c.column_id
, c.name
FROM
sys.[columns] c
ORDER BY
c.[object_id]
, c.column_id
DECLARE @id INT = 10
SELECT
*
FROM
@Tbl t
WHERE
EXISTS ( SELECT
1
FROM
@Tbl t2
WHERE
t.id < t2.id
AND t.Mainid = t2.Mainid
AND EXISTS ( SELECT
1
FROM
@Tbl t3
WHERE
t3.id = @id
AND t2.Mainid = t3.Mainid ) )
SELECT
*
FROM
@Tbl t
WHERE
t.id NOT IN ( SELECT
MAX(t2.id)
FROM
@Tbl t2
INNER JOIN @Tbl t3
ON t3.Mainid = t2.Mainid
WHERE
t.Mainid = t2.Mainid
AND t3.id = @id )
gsc_dba
-
but the scan count that's what make me worry when there is no primary key or unique key,
yes and i have the primary key in my original table for an another column
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url] -
gsc_dba (8/14/2013)
Having had a brief look, the second query (on my PC) was better in terms of CPU and fewer reads.Yes, same for me. But this one blows them both out the water:
WITH NumberedColumns (ID, Mainid, Subid, NAME, RowNo) AS (SELECT
ID
,Mainid
,Subid
,NAME
,ROW_NUMBER() OVER (PARTITION BY Mainid ORDER BY Subid Desc)
FROM
@Tbl
WHERE
Mainid = (SELECT
MainID
FROM
@Tbl
WHERE
ID = @id)
)
SELECT
ID
,Mainid
,Subid
,NAME
FROM
NumberedColumns
WHERE
RowNo > 1
Here are the results, in order:
Scan count 3, logical reads 13817, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.CPU time = 281 ms, elapsed time = 284 ms.
Scan count 674, logical reads 2022, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
CPU time = 47 ms, elapsed time = 37 ms.
Scan count 2, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
CPU time = 0 ms, elapsed time = 0 ms.
John
-
If scan counts are a concern, you could add a primary key for all columns (as you are using a SELECT * FROM)
DECLARE @Tbl AS TABLE(
id INT IDENTITY(1, 1)
, Mainid INT
, Subid INT
, NAME VARCHAR(100)
, PRIMARY KEY (id
, mainid, subid, name))
Edit
In terms of performance for IO, the primary key on id is the better...
gsc_dba
-
By the way, why are you using a table variable at all? If your database has a lot of tables, you're pulling out a lot of rows that you're just going to throw away. Why not run the query directly against sys.columns?
John
Edit: also, the way your query is written, you're not guaranteed to get the same results every time. That's because the order the rows will be inserted into the table variable is not guaranteed.
-
@john-2 Mitchell-245523
Thanks that was superb, this is why i posted here, a new logic in different dimension actually i use my base table in Devdb
thanks for the info
@gsc_dba
since My dba didn't allow a wide primary key i was not able to do that one
any way thank you guys
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply