January 17, 2008 at 12:58 pm
A lot of our procedures have optional parameters and instead of doing a scan on an index is there a way to get the seek without if conditions and/or dynamic sql? For this example I've setup a table with the least amount of what I'm looking for, forgive the 'select *'
SET NOCOUNT ON
GO
IF OBJECT_ID('ScanSeekTable') IS NOT NULL
DROP TABLE ScanSeekTable
GO
CREATE TABLE ScanSeekTable(
idINT IDENTITY,
codeCHAR(10),
param1INT,
param2INT,
CONSTRAINT pkScanSeekTable PRIMARY KEY(id),
CONSTRAINT unqScanSeekTableCode UNIQUE(code)
)
GO
CREATE INDEX idxScanSeekTableP1 ON ScanSeekTable(param1)
GO
CREATE INDEX idxScanSeekTableP2 ON ScanSeekTable(param2)
GO
INSERT INTO ScanSeekTable(code, param1, param2)
SELECT 'CODE', 1, 2
GO
BEGIN
DECLARE @p1 INT, @p2 INT
SELECT @p1 = 1, @p2 = null
SELECT *
FROM ScanSeekTable
WHERE
(@p1 IS NULL OR param1 = @p1) AND
(@p2 IS NULL OR param2 = @p2)
SELECT *
FROM ScanSeekTable
WHERE
param1 = COALESCE(@p1, param1) AND
param2 = COALESCE(@p2, param2)
IF @p1 IS NULL AND @p2 IS null
SELECT *
FROM ScanSeekTable
ELSE IF @p1 IS null
SELECT *
FROM ScanSeekTable
WHERE
param2 = @p2
ELSE IF @p2 IS NULL
SELECT *
FROM ScanSeekTable
WHERE
param1 = @p1
ELSE
SELECT *
FROM ScanSeekTable
WHERE
param1 = @p1 and
param2 = @p2
DECLARE
@sqlNVARCHAR(500),
@PdNVARCHAR(500)
SET @sql = N'SELECT * from ScanSeekTable'
SET @Pd = '@pdP1 int, @pdP2 int'
IF @p1 IS NOT NULL OR @p2 IS NOT NULL
BEGIN
IF @p1 IS NOT NULL
BEGIN
SET @sql = @sql + N'param1 = @pdP1 '
IF @p2 IS NOT NULL
END
IF @p2 IS NOT NULL
SET @sql = @sql + N'param2 = @pdP2'
END
EXEC sp_executesql @sql,
@Pd,
@pdP1 = @p1,
@pdP2 = @p2
END
The first select statement is what we currently do.
The second select statement was a trick I found for Oracle(nvl) and thought it could trick T-SQL.
The third is the fact that it does the seek but have to have the if's.
The last is the mess of dynamic SQL.
Any ideas on how to get the seek from 3 or 4 without the extra work?
This is just an example, there are times where this will be multiple joins and having scans more often than desired.
January 17, 2008 at 3:17 pm
One issue you're going to have with trying to test for seeks IS the select * (unfortunately:)). It's going to skew your results away from seeks. Also - in this case - your index is never really going to be an attractive scenario, since a. you need all fields, and your index key is relatively wide in relation to the overall table (a factor in deciding whether to use an index or not).
You need to throw in some fields you "don't care for" right now, so that the index looks like it might be "useful". Once you do that - you might find that you have some better results (more applicable to "real tables".
I also made sur the index at least covers the search criteria.
The solution is a spin-off from #2, except that you get seeks in all cases except for both params being null (which is then by definition a scan, since all rows need to be returned.)
drop table widetable
create table widetable(id int primary key identity(1,1),
coid int,
prodid int,
orderamount int,
noisefield char(100) default 'ppppp')
go
insert widetable (coid, prodid,orderamount)
select top 500000 coid,prodid,orderamount
from testpivot
create index ix_wise on widetable(coid,prodid)
create index ix_wise2 on widetable(prodid,coid)
go
declare @p1 int, @p2 int
dbcc freeproccache
select @p1=null , @p2=50
if (@p1 is not null)
select orderamount
from widetable
where coid=@p1
AND (prodid=case when @p2 is null then prodid else @p2 end)
else if (@p2 is not null)
select orderamount
from widetable
where prodid=@p2 AND
coid=case when @p1 is null then coid else @p1 end
else
select orderamount
from widetable
------------------------------------
dbcc freeproccache
select @p1=1 , @p2=null
if (@p1 is not null)
select orderamount
from widetable
where coid=@p1
AND (prodid=case when @p2 is null then prodid else @p2 end)
else if (@p2 is not null)
select orderamount
from widetable
where prodid=@p2 AND
coid=case when @p1 is null then coid else @p1 end
else
select orderamount
from widetable
------------------------------------
dbcc freeproccache
select @p1=null , @p2=null
if (@p1 is not null)
select orderamount
from widetable
where coid=@p1
AND (prodid=case when @p2 is null then prodid else @p2 end)
else if (@p2 is not null)
select orderamount
from widetable
where prodid=@p2 AND
coid=case when @p1 is null then coid else @p1 end
else
select orderamount
from widetable
------------------------------------
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 18, 2008 at 6:27 am
ok, I've abbreviated more of my real code. Not all the fields, indexes, etc are included but maybe this can get my point accross a little better. Any and all help is appreciated. Also, please, make sure this is SQL2K compliant but of course I'm still interested in 2K5 answers as we'll be going there soon enough.
IF(OBJECT_ID('r_cmp_grp') IS NOT NULL)
DROP TABLE r_cmp_grp
IF(OBJECT_ID('flows') IS NOT NULL)
DROP TABLE flows
IF(OBJECT_ID('accounts') IS NOT NULL)
DROP TABLE accounts
IF(OBJECT_ID('groups') IS NOT NULL)
DROP TABLE groups
IF(OBJECT_ID('companies') IS NOT NULL)
DROP TABLE companies
IF(OBJECT_ID('banks') IS NOT NULL)
DROP TABLE banks
GO
--ordinarily the id fields are identity but didn't want to go through the trouble of identity_insert for this example
CREATE TABLE companies(
id INT,
code CHAR(10),
NAME VARCHAR(50),
CONSTRAINT pkCompanies PRIMARY KEY(id),
CONSTRAINT unqCompaniesCode UNIQUE(code)
)
CREATE TABLE groups(
id INT,
code CHAR(10),
NAME VARCHAR(50),
CONSTRAINT pkGroups PRIMARY KEY(id),
CONSTRAINT unqGroupsCode UNIQUE(code)
)
CREATE TABLE r_cmp_grp(
cmp_id INT,
grp_id INT,
CONSTRAINT pkCmpGrp PRIMARY KEY(cmp_id, grp_id),
CONSTRAINT fkCmpGrpCmp FOREIGN KEY(cmp_id) REFERENCES companies(id),
CONSTRAINT fkCmpGrpGrp FOREIGN KEY(grp_id) REFERENCES groups(id)
)
CREATE TABLE banks(
id INT,
code CHAR(10),
NAME VARCHAR(50),
CONSTRAINT pkBanks PRIMARY KEY(id),
CONSTRAINT unqBanksCode UNIQUE(code)
)
CREATE TABLE accounts(
id INT,
code CHAR(10),
NAME VARCHAR(50),
bnk_id INT,
cmp_id INT,
CONSTRAINT pkAccounts PRIMARY KEY(id),
CONSTRAINT unqAccountsCode UNIQUE(code),
CONSTRAINT fkAccountsBnk FOREIGN KEY(bnk_id) REFERENCES banks(id),
CONSTRAINT fkAccountsCmp FOREIGN KEY(cmp_id) REFERENCES companies(id)
)
CREATE TABLE flows(
id INT IDENTITY,
ref CHAR(10),
dt DATETIME,
acc_id INT,
amt FLOAT,
CONSTRAINT pkFlows PRIMARY KEY(id),
CONSTRAINT fkFlowAcc FOREIGN KEY(acc_id) REFERENCES accounts(id)
)
CREATE INDEX idxFlowsAcc ON flows(dt, acc_id)
GO
BEGIN
INSERT INTO companies(id, code, NAME)
SELECT id, CAST(id AS CHAR(10)), NAME
FROM sysobjects
INSERT INTO groups(id, code, NAME)
SELECT id, CAST(id AS CHAR(10)), NAME
FROM sysobjects
INSERT INTO r_cmp_grp(cmp_id, grp_id)
SELECT id, id
FROM sysobjects
INSERT INTO banks(id, code, NAME)
SELECT id, CAST(id AS CHAR(10)), NAME
FROM sysobjects
INSERT INTO accounts(code, NAME, bnk_id, cmp_id, id)
SELECT CAST(id AS CHAR(10)), NAME, id, id, id
FROM sysobjects
INSERT INTO flows(ref, dt, acc_id, amt)
SELECT UPPER(left(NAME,10)), DATEADD(DAY, id % 50, GETDATE()), id, (status + RAND() / 3)
FROM sysobjects
END
GO
BEGIN
DECLARE
@BnkIdINT,
@AccIdINT,
@CmpIdINT,
@GrpIdINT
SELECT f.dt, f.amt, bnk.code, acc.code, cmp.code
FROM flows f
INNER JOIN accounts acc ON acc.id = f.acc_id
INNER JOIN banks bnk ON bnk.id = acc.bnk_id
INNER JOIN companies cmp ON cmp.id = acc.cmp_id
--do exists because I only want the transaction flow once!
WHERE EXISTS(SELECT * FROM r_cmp_grp r WHERE r.cmp_id = cmp.id AND (@grpId IS NULL OR @grpId = r.grp_id))
AND (@AccId IS NULL OR f.acc_id = @AccId)
AND (@BnkId IS NULL OR acc.bnk_id = @BnkId)
AND (@cmpId IS NULL OR acc.cmp_id = @cmpId)
ORDER BY bnk.code, acc.code, f.dt
--Feel free to use previous example on using different ways to get a seek rather than these scans
END
GO
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply