Hi,
I have a table like this:
tblDescendants
ParentID ChildID
A B
A C
B D
D E
So this table contains parent-child relations, but they can go infinite
long.
What I need is a select-query, that returns me for al the 'original'
parents, their relatiosn with their cildren, grandchildren, and further
descendants etc...
So with this records:
ParentID DescendantID
A B
A C
A D (because D is a child of B which is a child of A)
A E (same reason).
How can I do this? I need to use this select in a join. I have a solution
with a temporary table, but I would prefer not to have to make this
temporary table each time I run my query... I would prefer a solution with
an Indexed View.
Does anybody knows how? Any help will be really appreciated!
Thansk a lot in advance,
PieterHi
Take a look at Itzik Ben-Gan's examples
IF object_id('dbo.Employees') IS NOT NULL
DROP TABLE Employees
GO
IF object_id('dbo.ufn_GetSubtree') IS NOT NULL
DROP FUNCTION dbo.ufn_GetSubtree
GO
CREATE TABLE Employees
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
CONSTRAINT PK_Employees_empid PRIMARY KEY(empid),
CONSTRAINT FK_Employees_mgrid_empid
FOREIGN KEY(mgrid)
REFERENCES Employees(empid)
)
CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00)
INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00)
INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00)
INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00)
INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00)
INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00)
INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00)
INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00)
INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00)
INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00)
INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00)
INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00)
INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00)
INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00)
GO
CREATE FUNCTION dbo.ufn_GetSubtree
(
@.mgrid AS int
)
RETURNS @.tree table
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
lvl int NOT NULL,
path varchar(900) NOT NULL
)
AS
BEGIN
DECLARE @.lvl AS int, @.path AS varchar(900)
SELECT @.lvl = 0, @.path = '.'
INSERT INTO @.tree
SELECT empid, mgrid, empname, salary,
@.lvl, '.' + CAST(empid AS varchar(10)) + '.'
FROM Employees
WHERE empid = @.mgrid
WHILE @.@.ROWCOUNT > 0
BEGIN
SET @.lvl = @.lvl + 1
INSERT INTO @.tree
SELECT E.empid, E.mgrid, E.empname, E.salary,
@.lvl, T.path + CAST(E.empid AS varchar(10)) + '.'
FROM Employees AS E JOIN @.tree AS T
ON E.mgrid = T.empid AND T.lvl = @.lvl - 1
END
RETURN
END
GO
SELECT empid, mgrid, empname, salary
FROM ufn_GetSubtree(3)
GO
/*
empid mgrid empname salary
2 1 Andrew 5000.0000
5 2 Steven 2500.0000
6 2 Michael 2500.0000
*/
--SQL Server 2005--
With TreeCTE (empid, mgrid, empname, salary, lvl, [path]) as
(
select empid, mgrid, empname, salary, 0 as lvl, cast('' as varchar(200))
as [path]
from Employees
union all
select tc.empid, e.mgrid, tc.empname, tc.salary, lvl + 1,
cast([path]+cast(e.empid as varchar(20))+'/' as varchar(200))
from Employees e
inner join TreeCTE tc on e.empid = tc.mgrid
)
select * from TreeCTE where mgrid = 1
----
/*
SELECT REPLICATE (' | ', lvl) + empname AS employee
FROM ufn_GetSubtree(1)
ORDER BY path
*/
/*
employee
--
Nancy
| Andrew
| | Steven
| | Michael
| Janet
| | Robert
| | | David
| | | | James
| | | Ron
| | | Dan
| | Laura
| | Ann
| Margaret
| | Ina
*/
"Pieter" <pieterNOSPAMcoucke@.hotmail.com> wrote in message
news:%23orHcToIHHA.1064@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have a table like this:
> tblDescendants
> ParentID ChildID
> A B
> A C
> B D
> D E
>
> So this table contains parent-child relations, but they can go infinite
> long.
> What I need is a select-query, that returns me for al the 'original'
> parents, their relatiosn with their cildren, grandchildren, and further
> descendants etc...
> So with this records:
> ParentID DescendantID
> A B
> A C
> A D (because D is a child of B which is a child of A)
> A E (same reason).
> How can I do this? I need to use this select in a join. I have a solution
> with a temporary table, but I would prefer not to have to make this
> temporary table each time I run my query... I would prefer a solution with
> an Indexed View.
> Does anybody knows how? Any help will be really appreciated!
> Thansk a lot in advance,
> Pieter
>|||Ok thanks, putting it all in a function works :-)
Friday, March 30, 2012
query: child of child of child of...: Which are the descendants?
Hi,
I have a table like this:
tblDescendants
ParentID ChildID
A B
A C
B D
D E
So this table contains parent-child relations, but they can go infinite
long.
What I need is a select-query, that returns me for al the 'original'
parents, their relatiosn with their cildren, grandchildren, and further
descendants etc...
So with this records:
ParentID DescendantID
A B
A C
A D (because D is a child of B which is a child of A)
A E (same reason).
How can I do this? I need to use this select in a join. I have a solution
with a temporary table, but I would prefer not to have to make this
temporary table each time I run my query... I would prefer a solution with
an Indexed View.
Does anybody knows how? Any help will be really appreciated!
Thansk a lot in advance,
PieterHi
Take a look at Itzik Ben-Gan's examples
IF object_id('dbo.Employees') IS NOT NULL
DROP TABLE Employees
GO
IF object_id('dbo.ufn_GetSubtree') IS NOT NULL
DROP FUNCTION dbo.ufn_GetSubtree
GO
CREATE TABLE Employees
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
CONSTRAINT PK_Employees_empid PRIMARY KEY(empid),
CONSTRAINT FK_Employees_mgrid_empid
FOREIGN KEY(mgrid)
REFERENCES Employees(empid)
)
CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00)
INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00)
INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00)
INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00)
INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00)
INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00)
INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00)
INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00)
INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00)
INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00)
INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00)
INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00)
INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00)
INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00)
GO
CREATE FUNCTION dbo.ufn_GetSubtree
(
@.mgrid AS int
)
RETURNS @.tree table
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
lvl int NOT NULL,
path varchar(900) NOT NULL
)
AS
BEGIN
DECLARE @.lvl AS int, @.path AS varchar(900)
SELECT @.lvl = 0, @.path = '.'
INSERT INTO @.tree
SELECT empid, mgrid, empname, salary,
@.lvl, '.' + CAST(empid AS varchar(10)) + '.'
FROM Employees
WHERE empid = @.mgrid
WHILE @.@.ROWCOUNT > 0
BEGIN
SET @.lvl = @.lvl + 1
INSERT INTO @.tree
SELECT E.empid, E.mgrid, E.empname, E.salary,
@.lvl, T.path + CAST(E.empid AS varchar(10)) + '.'
FROM Employees AS E JOIN @.tree AS T
ON E.mgrid = T.empid AND T.lvl = @.lvl - 1
END
RETURN
END
GO
SELECT empid, mgrid, empname, salary
FROM ufn_GetSubtree(3)
GO
/*
empid mgrid empname salary
2 1 Andrew 5000.0000
5 2 Steven 2500.0000
6 2 Michael 2500.0000
*/
--SQL Server 2005--
With TreeCTE (empid, mgrid, empname, salary, lvl, [path]) as
(
select empid, mgrid, empname, salary, 0 as lvl, cast('' as varchar(200))
as [path]
from Employees
union all
select tc.empid, e.mgrid, tc.empname, tc.salary, lvl + 1,
cast([path]+cast(e.empid as varchar(20))+'/' as varchar(200))
from Employees e
inner join TreeCTE tc on e.empid = tc.mgrid
)
select * from TreeCTE where mgrid = 1
----
/*
SELECT REPLICATE (' | ', lvl) + empname AS employee
FROM ufn_GetSubtree(1)
ORDER BY path
*/
/*
employee
--
Nancy
| Andrew
| | Steven
| | Michael
| Janet
| | Robert
| | | David
| | | | James
| | | Ron
| | | Dan
| | Laura
| | Ann
| Margaret
| | Ina
*/
"Pieter" <pieterNOSPAMcoucke@.hotmail.com> wrote in message
news:%23orHcToIHHA.1064@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have a table like this:
> tblDescendants
> ParentID ChildID
> A B
> A C
> B D
> D E
>
> So this table contains parent-child relations, but they can go infinite
> long.
> What I need is a select-query, that returns me for al the 'original'
> parents, their relatiosn with their cildren, grandchildren, and further
> descendants etc...
> So with this records:
> ParentID DescendantID
> A B
> A C
> A D (because D is a child of B which is a child of A)
> A E (same reason).
> How can I do this? I need to use this select in a join. I have a solution
> with a temporary table, but I would prefer not to have to make this
> temporary table each time I run my query... I would prefer a solution with
> an Indexed View.
> Does anybody knows how? Any help will be really appreciated!
> Thansk a lot in advance,
> Pieter
>|||Ok thanks, putting it all in a function works :-)sql
I have a table like this:
tblDescendants
ParentID ChildID
A B
A C
B D
D E
So this table contains parent-child relations, but they can go infinite
long.
What I need is a select-query, that returns me for al the 'original'
parents, their relatiosn with their cildren, grandchildren, and further
descendants etc...
So with this records:
ParentID DescendantID
A B
A C
A D (because D is a child of B which is a child of A)
A E (same reason).
How can I do this? I need to use this select in a join. I have a solution
with a temporary table, but I would prefer not to have to make this
temporary table each time I run my query... I would prefer a solution with
an Indexed View.
Does anybody knows how? Any help will be really appreciated!
Thansk a lot in advance,
PieterHi
Take a look at Itzik Ben-Gan's examples
IF object_id('dbo.Employees') IS NOT NULL
DROP TABLE Employees
GO
IF object_id('dbo.ufn_GetSubtree') IS NOT NULL
DROP FUNCTION dbo.ufn_GetSubtree
GO
CREATE TABLE Employees
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
CONSTRAINT PK_Employees_empid PRIMARY KEY(empid),
CONSTRAINT FK_Employees_mgrid_empid
FOREIGN KEY(mgrid)
REFERENCES Employees(empid)
)
CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00)
INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00)
INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00)
INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00)
INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00)
INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00)
INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00)
INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00)
INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00)
INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00)
INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00)
INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00)
INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00)
INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00)
GO
CREATE FUNCTION dbo.ufn_GetSubtree
(
@.mgrid AS int
)
RETURNS @.tree table
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
lvl int NOT NULL,
path varchar(900) NOT NULL
)
AS
BEGIN
DECLARE @.lvl AS int, @.path AS varchar(900)
SELECT @.lvl = 0, @.path = '.'
INSERT INTO @.tree
SELECT empid, mgrid, empname, salary,
@.lvl, '.' + CAST(empid AS varchar(10)) + '.'
FROM Employees
WHERE empid = @.mgrid
WHILE @.@.ROWCOUNT > 0
BEGIN
SET @.lvl = @.lvl + 1
INSERT INTO @.tree
SELECT E.empid, E.mgrid, E.empname, E.salary,
@.lvl, T.path + CAST(E.empid AS varchar(10)) + '.'
FROM Employees AS E JOIN @.tree AS T
ON E.mgrid = T.empid AND T.lvl = @.lvl - 1
END
RETURN
END
GO
SELECT empid, mgrid, empname, salary
FROM ufn_GetSubtree(3)
GO
/*
empid mgrid empname salary
2 1 Andrew 5000.0000
5 2 Steven 2500.0000
6 2 Michael 2500.0000
*/
--SQL Server 2005--
With TreeCTE (empid, mgrid, empname, salary, lvl, [path]) as
(
select empid, mgrid, empname, salary, 0 as lvl, cast('' as varchar(200))
as [path]
from Employees
union all
select tc.empid, e.mgrid, tc.empname, tc.salary, lvl + 1,
cast([path]+cast(e.empid as varchar(20))+'/' as varchar(200))
from Employees e
inner join TreeCTE tc on e.empid = tc.mgrid
)
select * from TreeCTE where mgrid = 1
----
/*
SELECT REPLICATE (' | ', lvl) + empname AS employee
FROM ufn_GetSubtree(1)
ORDER BY path
*/
/*
employee
--
Nancy
| Andrew
| | Steven
| | Michael
| Janet
| | Robert
| | | David
| | | | James
| | | Ron
| | | Dan
| | Laura
| | Ann
| Margaret
| | Ina
*/
"Pieter" <pieterNOSPAMcoucke@.hotmail.com> wrote in message
news:%23orHcToIHHA.1064@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have a table like this:
> tblDescendants
> ParentID ChildID
> A B
> A C
> B D
> D E
>
> So this table contains parent-child relations, but they can go infinite
> long.
> What I need is a select-query, that returns me for al the 'original'
> parents, their relatiosn with their cildren, grandchildren, and further
> descendants etc...
> So with this records:
> ParentID DescendantID
> A B
> A C
> A D (because D is a child of B which is a child of A)
> A E (same reason).
> How can I do this? I need to use this select in a join. I have a solution
> with a temporary table, but I would prefer not to have to make this
> temporary table each time I run my query... I would prefer a solution with
> an Indexed View.
> Does anybody knows how? Any help will be really appreciated!
> Thansk a lot in advance,
> Pieter
>|||Ok thanks, putting it all in a function works :-)sql
QUERY: 1 SQL Server restricted access according to user account...
Is it possible to run only 1 SQL Server 2000/2005 and set it up so that it
displays specific databases corresponding to the user account logged in?
Not in SQL 2000, but very 'doable' in SQL 2005.
Read up on using 'Schemas' in Books Online.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Andrew Wan" <andrew_wan1980@.hotmail.com> wrote in message
news:%23qRRmtFJHHA.1424@.TK2MSFTNGP04.phx.gbl...
> Is it possible to run only 1 SQL Server 2000/2005 and set it up so that it
> displays specific databases corresponding to the user account logged in?
>
displays specific databases corresponding to the user account logged in?
Not in SQL 2000, but very 'doable' in SQL 2005.
Read up on using 'Schemas' in Books Online.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Andrew Wan" <andrew_wan1980@.hotmail.com> wrote in message
news:%23qRRmtFJHHA.1424@.TK2MSFTNGP04.phx.gbl...
> Is it possible to run only 1 SQL Server 2000/2005 and set it up so that it
> displays specific databases corresponding to the user account logged in?
>
QUERY: 1 SQL Server restricted access according to user account...
Is it possible to run only 1 SQL Server 2000/2005 and set it up so that it
displays specific databases corresponding to the user account logged in?Not in SQL 2000, but very 'doable' in SQL 2005.
Read up on using 'Schemas' in Books Online.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Andrew Wan" <andrew_wan1980@.hotmail.com> wrote in message
news:%23qRRmtFJHHA.1424@.TK2MSFTNGP04.phx.gbl...
> Is it possible to run only 1 SQL Server 2000/2005 and set it up so that it
> displays specific databases corresponding to the user account logged in?
>
displays specific databases corresponding to the user account logged in?Not in SQL 2000, but very 'doable' in SQL 2005.
Read up on using 'Schemas' in Books Online.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Andrew Wan" <andrew_wan1980@.hotmail.com> wrote in message
news:%23qRRmtFJHHA.1424@.TK2MSFTNGP04.phx.gbl...
> Is it possible to run only 1 SQL Server 2000/2005 and set it up so that it
> displays specific databases corresponding to the user account logged in?
>
QUERY: 1 SQL Server restricted access according to user account...
Is it possible to run only 1 SQL Server 2000/2005 and set it up so that it
displays specific databases corresponding to the user account logged in?Not in SQL 2000, but very 'doable' in SQL 2005.
Read up on using 'Schemas' in Books Online.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Andrew Wan" <andrew_wan1980@.hotmail.com> wrote in message
news:%23qRRmtFJHHA.1424@.TK2MSFTNGP04.phx.gbl...
> Is it possible to run only 1 SQL Server 2000/2005 and set it up so that it
> displays specific databases corresponding to the user account logged in?
>
displays specific databases corresponding to the user account logged in?Not in SQL 2000, but very 'doable' in SQL 2005.
Read up on using 'Schemas' in Books Online.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Andrew Wan" <andrew_wan1980@.hotmail.com> wrote in message
news:%23qRRmtFJHHA.1424@.TK2MSFTNGP04.phx.gbl...
> Is it possible to run only 1 SQL Server 2000/2005 and set it up so that it
> displays specific databases corresponding to the user account logged in?
>
Query/Workload Generator
Hello,
- Is any body aware of a random workload/query generator such as the
TPC-H query generator (QGEN) ? I am looking for a query generator that
takes a schema as an input, and produces several queries.
I need it mainly for performance evaluation. the generator I am looking
for should produce SELECT queries, including joins, and not only INSERT
INTO, DELETE, UPDATE queries such as the "SqlQueryGenerator" at
http://www.tucows.com/preview/297930
Thanks
Regards,
Abdur-RahmanThe SQL 2000 Resource Kit has a tool called DB Hammer (or something
similar) which you can use to generate workloads. But I've never used
it myself, so I can't tell you if it's as easy as just pointing it at a
schema.
A general problem with tools which generate artificial workloads is
that they may not reflect your real workload patterns very well -
there's usually no way for the tool to know which tables or procs are
most frequently accessed. So another approach you might consider is
using Profiler to get a workload trace, then replay it in a test
environment while you gather whatever performance information you need.
Simon|||The SQL 2000 Resource Kit has a tool called DB Hammer (or something
similar) which you can use to generate workloads. But I've never used
it myself, so I can't tell you if it's as easy as just pointing it at a
schema.
A general problem with tools which generate artificial workloads is
that they may not reflect your real workload patterns very well -
there's usually no way for the tool to know which tables or procs are
most frequently accessed. So another approach you might consider is
using Profiler to get a workload trace, then replay it in a test
environment while you gather whatever performance information you need.
Simon
Query/View: The 2 newest periods for each indicator
Hi,
I'm working on a simple performance-program, where I need to extract
information from the 2 newest periods for every performance-indicator
- And from there calculate a trend between these results.
The problem is, that I can't find a simple way to extract the 2 latest
results.
The Table (Table1) looks like this:
kpiIDperiodIDActual
Acceleration23
Acceleration54
Speed1100
Speed4200
Speed7220
Speed9180
Weight122
Weight332
Weight721
Weight1033
If I want to extract the newest I use something like this (made it in
MS Access, so the syntax might differ slightly from SQLServer):
SELECT table1.kpiID, table1.periodID, table1.Actual
FROM table1 WHERE table1.periodID = (SELECT max(t.periodID) from
table1 as t WHERE t.kpiID=table1.kpiID);
BUT - how how do I get the second-newest period as well?
Preferably I would like the final result to be a View with the
following fields:
kpiID, periodID_newest, Actual_newest, periodID_sec_newest,
Actual_sec_newest
Alternatively a View with 2 posts for each performace-indicator.
Thanks in advance
RyanOn Fri, 24 Mar 2006 23:08:18 +0100, Ryan Dahl wrote:
>Hi,
>I'm working on a simple performance-program, where I need to extract
>information from the 2 newest periods for every performance-indicator
>- And from there calculate a trend between these results.
>The problem is, that I can't find a simple way to extract the 2 latest
>results.
>The Table (Table1) looks like this:
>kpiIDperiodIDActual
>Acceleration23
>Acceleration54
>Speed1100
>Speed4200
>Speed7220
>Speed9180
>Weight122
>Weight332
>Weight721
>Weight1033
>If I want to extract the newest I use something like this (made it in
>MS Access, so the syntax might differ slightly from SQLServer):
>SELECT table1.kpiID, table1.periodID, table1.Actual
>FROM table1 WHERE table1.periodID = (SELECT max(t.periodID) from
>table1 as t WHERE t.kpiID=table1.kpiID);
>BUT - how how do I get the second-newest period as well?
Hi Ryan,
SELECT a.kpiID, a.periodID, a.Actual
FROM table1 AS a
WHERE (SELECT COUNT(*)
FROM table1 AS b
WHERE b.kpiID = a.kpiID
AND b.periodID >= a.periodID) <= 2
>Preferably I would like the final result to be a View with the
>following fields:
>kpiID, periodID_newest, Actual_newest, periodID_sec_newest,
>Actual_sec_newest
In that case, try this instead:
SELECT a.kpiID, a.periodID, a.Actual, b.periodID, b.Actual
FROM table1 AS a
LEFT JOIN table1 AS b
ON b.kpiID = a.kpiID
AND b.periodID = (SELECT MAX(c.periodID)
FROM table1 AS c
WHERE c.kpiID = a.kpiID
AND c.periodID < a.periodID)
WHERE a.periodID = (SELECT MAX(t.periodID)
FROM table1 AS t
WHERE t.kpiID = a.kpiID)
(Both queries above are untested - see www.aspfaq.com/5006 if you prefer
a tested reply).
--
Hugo Kornelis, SQL Server MVP|||Hi Hugo,
Thanks a lot. I got them both working without any hassle.
>SELECT a.kpiID, a.periodID, a.Actual
>FROM table1 AS a
>WHERE (SELECT COUNT(*)
> FROM table1 AS b
> WHERE b.kpiID = a.kpiID
> AND b.periodID >= a.periodID) <= 2
I find this to be quite clever - had to look at it some time to figure
out how it works.
>>
>>Preferably I would like the final result to be a View with the
>>following fields:
>>kpiID, periodID_newest, Actual_newest, periodID_sec_newest,
>>Actual_sec_newest
>In that case, try this instead:
>SELECT a.kpiID, a.periodID, a.Actual, b.periodID, b.Actual
>FROM table1 AS a
>LEFT JOIN table1 AS b
> ON b.kpiID = a.kpiID
> AND b.periodID = (SELECT MAX(c.periodID)
> FROM table1 AS c
> WHERE c.kpiID = a.kpiID
> AND c.periodID < a.periodID)
>WHERE a.periodID = (SELECT MAX(t.periodID)
> FROM table1 AS t
> WHERE t.kpiID = a.kpiID)
Works as well - minor adjustment needed: Move lines 5-8 to the end.
Regards
Ryan|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.
I am a little confused on this. Aren't "acceleration", "speed", and
"weight" attributes and not values? Surely you are not mixing
meteadata and data.|||celko, have you actually ever HELPED anyone on this list???
i'd be curious to review a link where your original SQL code, written
in the past 10 years, is deomonstrated.
thx,
doug|||On Sat, 25 Mar 2006 08:21:38 +0100, Ryan Dahl wrote:
>>SELECT a.kpiID, a.periodID, a.Actual, b.periodID, b.Actual
>>FROM table1 AS a
>>LEFT JOIN table1 AS b
>> ON b.kpiID = a.kpiID
>> AND b.periodID = (SELECT MAX(c.periodID)
>> FROM table1 AS c
>> WHERE c.kpiID = a.kpiID
>> AND c.periodID < a.periodID)
>>WHERE a.periodID = (SELECT MAX(t.periodID)
>> FROM table1 AS t
>> WHERE t.kpiID = a.kpiID)
>>
>Works as well - minor adjustment needed: Move lines 5-8 to the end.
Hi Ryan,
That changes the meaning of the query - the place where you put those
lines dictates what will happen for a kpiID that has only one row.
This one row is by definition the latest - but there's no second latest.
If you use the query I suggested, you'll get this kpiID in your result,
with it's only row as last measurement and NULLs as it's second latest
measurement.
Your version (after moving those rows) will exclude any kpiID with only
one row. Only kpiIDs with two or more measurements will be displayed. If
that is indeed your requirement, then you can safely move these lines.
And you can change the LEFT JOIN in an INNER JOIN as well, to get some
performance gain.
--
Hugo Kornelis, SQL Server MVP|||On Mon, 27 Mar 2006 23:48:50 +0200, Hugo Kornelis
<hugo@.perFact.REMOVETHIS.info.INVALID> wrote:
>On Sat, 25 Mar 2006 08:21:38 +0100, Ryan Dahl wrote:
>>>SELECT a.kpiID, a.periodID, a.Actual, b.periodID, b.Actual
>>>FROM table1 AS a
>>>LEFT JOIN table1 AS b
>>> ON b.kpiID = a.kpiID
>>> AND b.periodID = (SELECT MAX(c.periodID)
>>> FROM table1 AS c
>>> WHERE c.kpiID = a.kpiID
>>> AND c.periodID < a.periodID)
>>>WHERE a.periodID = (SELECT MAX(t.periodID)
>>> FROM table1 AS t
>>> WHERE t.kpiID = a.kpiID)
>>>
>>Works as well - minor adjustment needed: Move lines 5-8 to the end.
>Hi Ryan,
>That changes the meaning of the query - the place where you put those
>lines dictates what will happen for a kpiID that has only one row.
>This one row is by definition the latest - but there's no second latest.
>If you use the query I suggested, you'll get this kpiID in your result,
>with it's only row as last measurement and NULLs as it's second latest
>measurement.
>Your version (after moving those rows) will exclude any kpiID with only
>one row. Only kpiIDs with two or more measurements will be displayed. If
>that is indeed your requirement, then you can safely move these lines.
>And you can change the LEFT JOIN in an INNER JOIN as well, to get some
>performance gain.
Hi Hugo,
thanks for pointing this out. SQLServer accepted without any problems.
As mentioned earlier I tested on MS Access, and it seems that it
doesn't support this join-type (no error-description of any kind) so I
made the mistake of assuming there was a small error in the
sql-string.
Thanks again.
Ryan|||I've read 2 books written by joe celko, and and both have really helped
me. I frequently profile my code to see what it is doing. So he's
helped me.|||Go to the CMP archives for DBMS, DATABASE PROGRAMMING & DESIGN, and
INTELLIGENT ENTERPRISE magazines to go back over ten years. I have
written over 750 columns in the computer trade and academic press,
mostly dealing with data and databases. I currently write for BMC's
DBAzine.com e-magazine.
My six books: SQL FOR SMARTIES (Morgan-Kaufmann, 1995, second edition
1999, third edition 2005), SQL PUZZLES & ANSWERS (Morgan-Kaufmann,
1997), DATA & DATABASES (Morgan-Kaufmann, 1999) and TREES & HIERARCHIES
IN SQL (Morgan-Kaufmann, 2004) and SQL PROGRAMMING STYLE
Morgan-Kaufmann, 2005).
Past magazine columns include: "SQL Explorer" in DBMS (Miller-Freeman);
"Celko on SQL" in DATABASE PROGRAMMING & DESIGN (Miller-Freeman);
"WATCOM SQL Corner" in POWERBUILDER DEVELOPERS' JOURNAL (SysCon); "SQL
Puzzle" in BOXES & ARROWS (Frank Sweet Publishing); "DBMS/Report" in
SYSTEMS INTEGRATION (Cahner-Ziff); "Data Desk" in TECH SPECIALIST
(R&D); "Data Points" in PC TECHNIQUES (Coriolis Group); "Celko on
Software" in COMPUTING (VNC Publications, UK), "SELECT * FROM Austin"
(Array Publications, The Netherlands), and he was editor for the
"Puzzles & Problems" section of ABACUS (Springer-Verlag) and I ran the
CASEFORUM section 18, "Celko on SQL", on CompuServe.
So, what have you done?|||if it is SQL Server 2005, use row_number() *untested*:
select * from(
SELECT table1.kpiID, table1.periodID, table1.Actual,
row_number() over(partition by kpId order by periodId desc) rn
FROM table1
) t
where rn<3|||That's Joe Celko, the demi-god of SQL for pete's sake.sql
Query/View Question
I am trying to create a view that returns data from three tables and can't seem to get it to return the data that I want. I am no SQL expert, so hopefully someone can give me some insight into what I need to do.
The tables are basically set up like this:
TABLE 1
PrimaryKey
Textfield1
Textfield2
Textfield3
TABLE 2
PrimaryKey
Table1ForeignKey
Table3ForeignKey
Textfield1
TABLE 3
PrimaryKey
Textfield1
Textfield2
Textfield3
Table 1 and Table 3 are each joined to Table 2 on their respective Primary/Foreign Key fields.
I want the view to return all of the records from Table 1, even if there are no matching records in Table 2.
From Table 2 I only want the latest record for each record in Table 1.
I want the view to look something like this:
Table 1
PrimaryKey
Table1
Textfield1
Table2
Textfield
Table3
Textfield
In other words, I want to return one record in the view for each record in table 1, and I want the data from table 2 in each of those records to represent the last record added to table 2.
Can anyone enlighten me on the query necessary to get this view?
Hi,
some more questions:
how do you define "the latest" in table2 ?
HTH, Jens Suessmeyer,
http://www.sqlserver2005.de
|||Since the Primary Key field autoincrements, the 'latest' record from Table 2 will always be the max(table2.primarykey).|||Perhaps my question will make more sense explained like this:
I will use an analogy of checking out books from the library.
Table 1 is a table of books, with a primary key of bookid.
Table 2 is a detail record of who withdrew the book, when, when it was returned, etc. with a primary key of DetailID and has a foreign key to Table 1 to identify the book as well as a foreign key to table 3 to identify who withdrew it.
Table 3 is a table of library card holders contact info with a primary key of CardholderID.
All of the primary keys are auto-incrementing.
I want the view to basically give me a snapshot of ALL books, and if it a particular book is currently withdrawn, I want to see who has it and when they checked it out.
I hope that makes more sense.
|||OK, keeping your analogy in mind, the query should be like:
Select
T1.PrimaryKey,T1.TextField,
T2.PrimaryKey,T2.TextField,
T3.TextField
FROM Table1 T1
LEFT JOIN
(
SELECT Table1FK, Table3FK,Textfield
FROM Table2
INNER JOIN
(
SELECT MAX(PrimaryKey) as PK, Table1PK
FROM TABLE2
GROUP BY Table1PK
) SubQuery
ON Subquery.PK = Table2.PK
AND SubQuery.Table1PK = Table2.Table1PK
) T2
ON
T1.PrimaryKey = T2.Table1FK
INNER JOIN Table3 T3
ON T3.PrimaryKey = T2.Table1FK
untested....
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
Query/test for db_owner role?
Is there a SQL query that can explicitly return the roles that the current
SQL user has been explicitly assigned? I'm trying to find out if the user
has been given the db_owner role (versus it being implied by the user being
an administrator).
Thanks!
What version of SQL server?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"R Baker" <nospamx@.nospamx.com> wrote in message news:%239MeR9VPGHA.3944@.tk2msftngp13.phx.gbl...
> Is there a SQL query that can explicitly return the roles that the current
> SQL user has been explicitly assigned? I'm trying to find out if the user
> has been given the db_owner role (versus it being implied by the user being
> an administrator).
> Thanks!
>
|||Would this work for you?
sp_helprolemember 'db_owner'
Ben Nevarez, MCDBA, OCP
Database Administrator
"R Baker" wrote:
> Is there a SQL query that can explicitly return the roles that the current
> SQL user has been explicitly assigned? I'm trying to find out if the user
> has been given the db_owner role (versus it being implied by the user being
> an administrator).
> Thanks!
>
>
|||Yes, that might. I'll have to figure out how to process the result set using
the MFC CDatabase classes, but it seems doable.
Thanks.
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:33F82F3D-CDA3-4040-B160-22E6D96B3D55@.microsoft.com...[vbcol=seagreen]
> Would this work for you?
> sp_helprolemember 'db_owner'
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "R Baker" wrote:
|||SQL 2000 or SQL 2005 (mainly SQL 2000 for now).
Thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:erz8UAWPGHA.3576@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> What version of SQL server?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "R Baker" <nospamx@.nospamx.com> wrote in message
> news:%239MeR9VPGHA.3944@.tk2msftngp13.phx.gbl...
|||It is only one resultset, so you would process it like any resultset (as if you were sending a
SELECT from your app, or executing your own stored procedure which returns a result set).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"R Baker" <nospamx@.nospamx.com> wrote in message news:eTB3u8WPGHA.3936@.TK2MSFTNGP10.phx.gbl...
> Yes, that might. I'll have to figure out how to process the result set using the MFC CDatabase
> classes, but it seems doable.
> Thanks.
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:33F82F3D-CDA3-4040-B160-22E6D96B3D55@.microsoft.com...
>
SQL user has been explicitly assigned? I'm trying to find out if the user
has been given the db_owner role (versus it being implied by the user being
an administrator).
Thanks!
What version of SQL server?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"R Baker" <nospamx@.nospamx.com> wrote in message news:%239MeR9VPGHA.3944@.tk2msftngp13.phx.gbl...
> Is there a SQL query that can explicitly return the roles that the current
> SQL user has been explicitly assigned? I'm trying to find out if the user
> has been given the db_owner role (versus it being implied by the user being
> an administrator).
> Thanks!
>
|||Would this work for you?
sp_helprolemember 'db_owner'
Ben Nevarez, MCDBA, OCP
Database Administrator
"R Baker" wrote:
> Is there a SQL query that can explicitly return the roles that the current
> SQL user has been explicitly assigned? I'm trying to find out if the user
> has been given the db_owner role (versus it being implied by the user being
> an administrator).
> Thanks!
>
>
|||Yes, that might. I'll have to figure out how to process the result set using
the MFC CDatabase classes, but it seems doable.
Thanks.
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:33F82F3D-CDA3-4040-B160-22E6D96B3D55@.microsoft.com...[vbcol=seagreen]
> Would this work for you?
> sp_helprolemember 'db_owner'
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "R Baker" wrote:
|||SQL 2000 or SQL 2005 (mainly SQL 2000 for now).
Thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:erz8UAWPGHA.3576@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> What version of SQL server?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "R Baker" <nospamx@.nospamx.com> wrote in message
> news:%239MeR9VPGHA.3944@.tk2msftngp13.phx.gbl...
|||It is only one resultset, so you would process it like any resultset (as if you were sending a
SELECT from your app, or executing your own stored procedure which returns a result set).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"R Baker" <nospamx@.nospamx.com> wrote in message news:eTB3u8WPGHA.3936@.TK2MSFTNGP10.phx.gbl...
> Yes, that might. I'll have to figure out how to process the result set using the MFC CDatabase
> classes, but it seems doable.
> Thanks.
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:33F82F3D-CDA3-4040-B160-22E6D96B3D55@.microsoft.com...
>
Query/test for db_owner role?
Is there a SQL query that can explicitly return the roles that the current
SQL user has been explicitly assigned? I'm trying to find out if the user
has been given the db_owner role (versus it being implied by the user being
an administrator).
Thanks!What version of SQL server?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"R Baker" <nospamx@.nospamx.com> wrote in message news:%239MeR9VPGHA.3944@.tk2msftngp13.phx.gbl...
> Is there a SQL query that can explicitly return the roles that the current
> SQL user has been explicitly assigned? I'm trying to find out if the user
> has been given the db_owner role (versus it being implied by the user being
> an administrator).
> Thanks!
>|||Would this work for you?
sp_helprolemember 'db_owner'
Ben Nevarez, MCDBA, OCP
Database Administrator
"R Baker" wrote:
> Is there a SQL query that can explicitly return the roles that the current
> SQL user has been explicitly assigned? I'm trying to find out if the user
> has been given the db_owner role (versus it being implied by the user being
> an administrator).
> Thanks!
>
>|||Yes, that might. I'll have to figure out how to process the result set using
the MFC CDatabase classes, but it seems doable.
Thanks.
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:33F82F3D-CDA3-4040-B160-22E6D96B3D55@.microsoft.com...
> Would this work for you?
> sp_helprolemember 'db_owner'
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "R Baker" wrote:
>> Is there a SQL query that can explicitly return the roles that the
>> current
>> SQL user has been explicitly assigned? I'm trying to find out if the user
>> has been given the db_owner role (versus it being implied by the user
>> being
>> an administrator).
>> Thanks!
>>|||SQL 2000 or SQL 2005 (mainly SQL 2000 for now).
Thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:erz8UAWPGHA.3576@.TK2MSFTNGP15.phx.gbl...
> What version of SQL server?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "R Baker" <nospamx@.nospamx.com> wrote in message
> news:%239MeR9VPGHA.3944@.tk2msftngp13.phx.gbl...
>> Is there a SQL query that can explicitly return the roles that the
>> current SQL user has been explicitly assigned? I'm trying to find out if
>> the user has been given the db_owner role (versus it being implied by the
>> user being an administrator).
>> Thanks!|||It is only one resultset, so you would process it like any resultset (as if you were sending a
SELECT from your app, or executing your own stored procedure which returns a result set).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"R Baker" <nospamx@.nospamx.com> wrote in message news:eTB3u8WPGHA.3936@.TK2MSFTNGP10.phx.gbl...
> Yes, that might. I'll have to figure out how to process the result set using the MFC CDatabase
> classes, but it seems doable.
> Thanks.
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:33F82F3D-CDA3-4040-B160-22E6D96B3D55@.microsoft.com...
>> Would this work for you?
>> sp_helprolemember 'db_owner'
>> Ben Nevarez, MCDBA, OCP
>> Database Administrator
>>
>> "R Baker" wrote:
>> Is there a SQL query that can explicitly return the roles that the current
>> SQL user has been explicitly assigned? I'm trying to find out if the user
>> has been given the db_owner role (versus it being implied by the user being
>> an administrator).
>> Thanks!
>>
>
SQL user has been explicitly assigned? I'm trying to find out if the user
has been given the db_owner role (versus it being implied by the user being
an administrator).
Thanks!What version of SQL server?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"R Baker" <nospamx@.nospamx.com> wrote in message news:%239MeR9VPGHA.3944@.tk2msftngp13.phx.gbl...
> Is there a SQL query that can explicitly return the roles that the current
> SQL user has been explicitly assigned? I'm trying to find out if the user
> has been given the db_owner role (versus it being implied by the user being
> an administrator).
> Thanks!
>|||Would this work for you?
sp_helprolemember 'db_owner'
Ben Nevarez, MCDBA, OCP
Database Administrator
"R Baker" wrote:
> Is there a SQL query that can explicitly return the roles that the current
> SQL user has been explicitly assigned? I'm trying to find out if the user
> has been given the db_owner role (versus it being implied by the user being
> an administrator).
> Thanks!
>
>|||Yes, that might. I'll have to figure out how to process the result set using
the MFC CDatabase classes, but it seems doable.
Thanks.
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:33F82F3D-CDA3-4040-B160-22E6D96B3D55@.microsoft.com...
> Would this work for you?
> sp_helprolemember 'db_owner'
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "R Baker" wrote:
>> Is there a SQL query that can explicitly return the roles that the
>> current
>> SQL user has been explicitly assigned? I'm trying to find out if the user
>> has been given the db_owner role (versus it being implied by the user
>> being
>> an administrator).
>> Thanks!
>>|||SQL 2000 or SQL 2005 (mainly SQL 2000 for now).
Thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:erz8UAWPGHA.3576@.TK2MSFTNGP15.phx.gbl...
> What version of SQL server?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "R Baker" <nospamx@.nospamx.com> wrote in message
> news:%239MeR9VPGHA.3944@.tk2msftngp13.phx.gbl...
>> Is there a SQL query that can explicitly return the roles that the
>> current SQL user has been explicitly assigned? I'm trying to find out if
>> the user has been given the db_owner role (versus it being implied by the
>> user being an administrator).
>> Thanks!|||It is only one resultset, so you would process it like any resultset (as if you were sending a
SELECT from your app, or executing your own stored procedure which returns a result set).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"R Baker" <nospamx@.nospamx.com> wrote in message news:eTB3u8WPGHA.3936@.TK2MSFTNGP10.phx.gbl...
> Yes, that might. I'll have to figure out how to process the result set using the MFC CDatabase
> classes, but it seems doable.
> Thanks.
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:33F82F3D-CDA3-4040-B160-22E6D96B3D55@.microsoft.com...
>> Would this work for you?
>> sp_helprolemember 'db_owner'
>> Ben Nevarez, MCDBA, OCP
>> Database Administrator
>>
>> "R Baker" wrote:
>> Is there a SQL query that can explicitly return the roles that the current
>> SQL user has been explicitly assigned? I'm trying to find out if the user
>> has been given the db_owner role (versus it being implied by the user being
>> an administrator).
>> Thanks!
>>
>
Query/test for db_owner role?
Is there a SQL query that can explicitly return the roles that the current
SQL user has been explicitly assigned? I'm trying to find out if the user
has been given the db_owner role (versus it being implied by the user being
an administrator).
Thanks!What version of SQL server?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"R Baker" <nospamx@.nospamx.com> wrote in message news:%239MeR9VPGHA.3944@.tk2msftngp13.phx.gb
l...
> Is there a SQL query that can explicitly return the roles that the current
> SQL user has been explicitly assigned? I'm trying to find out if the user
> has been given the db_owner role (versus it being implied by the user bein
g
> an administrator).
> Thanks!
>|||Would this work for you?
sp_helprolemember 'db_owner'
Ben Nevarez, MCDBA, OCP
Database Administrator
"R Baker" wrote:
> Is there a SQL query that can explicitly return the roles that the current
> SQL user has been explicitly assigned? I'm trying to find out if the user
> has been given the db_owner role (versus it being implied by the user bein
g
> an administrator).
> Thanks!
>
>|||Yes, that might. I'll have to figure out how to process the result set using
the MFC CDatabase classes, but it seems doable.
Thanks.
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:33F82F3D-CDA3-4040-B160-22E6D96B3D55@.microsoft.com...[vbcol=seagreen]
> Would this work for you?
> sp_helprolemember 'db_owner'
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "R Baker" wrote:
>|||SQL 2000 or SQL 2005 (mainly SQL 2000 for now).
Thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:erz8UAWPGHA.3576@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> What version of SQL server?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "R Baker" <nospamx@.nospamx.com> wrote in message
> news:%239MeR9VPGHA.3944@.tk2msftngp13.phx.gbl...|||It is only one resultset, so you would process it like any resultset (as if
you were sending a
SELECT from your app, or executing your own stored procedure which returns a
result set).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"R Baker" <nospamx@.nospamx.com> wrote in message news:eTB3u8WPGHA.3936@.TK2MSFTNGP10.phx.gbl.
.
> Yes, that might. I'll have to figure out how to process the result set usi
ng the MFC CDatabase
> classes, but it seems doable.
> Thanks.
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:33F82F3D-CDA3-4040-B160-22E6D96B3D55@.microsoft.com...
>
SQL user has been explicitly assigned? I'm trying to find out if the user
has been given the db_owner role (versus it being implied by the user being
an administrator).
Thanks!What version of SQL server?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"R Baker" <nospamx@.nospamx.com> wrote in message news:%239MeR9VPGHA.3944@.tk2msftngp13.phx.gb
l...
> Is there a SQL query that can explicitly return the roles that the current
> SQL user has been explicitly assigned? I'm trying to find out if the user
> has been given the db_owner role (versus it being implied by the user bein
g
> an administrator).
> Thanks!
>|||Would this work for you?
sp_helprolemember 'db_owner'
Ben Nevarez, MCDBA, OCP
Database Administrator
"R Baker" wrote:
> Is there a SQL query that can explicitly return the roles that the current
> SQL user has been explicitly assigned? I'm trying to find out if the user
> has been given the db_owner role (versus it being implied by the user bein
g
> an administrator).
> Thanks!
>
>|||Yes, that might. I'll have to figure out how to process the result set using
the MFC CDatabase classes, but it seems doable.
Thanks.
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:33F82F3D-CDA3-4040-B160-22E6D96B3D55@.microsoft.com...[vbcol=seagreen]
> Would this work for you?
> sp_helprolemember 'db_owner'
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "R Baker" wrote:
>|||SQL 2000 or SQL 2005 (mainly SQL 2000 for now).
Thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:erz8UAWPGHA.3576@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> What version of SQL server?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "R Baker" <nospamx@.nospamx.com> wrote in message
> news:%239MeR9VPGHA.3944@.tk2msftngp13.phx.gbl...|||It is only one resultset, so you would process it like any resultset (as if
you were sending a
SELECT from your app, or executing your own stored procedure which returns a
result set).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"R Baker" <nospamx@.nospamx.com> wrote in message news:eTB3u8WPGHA.3936@.TK2MSFTNGP10.phx.gbl.
.
> Yes, that might. I'll have to figure out how to process the result set usi
ng the MFC CDatabase
> classes, but it seems doable.
> Thanks.
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:33F82F3D-CDA3-4040-B160-22E6D96B3D55@.microsoft.com...
>
Query/table tweaking Help
I am not a programmer but have noticed in this query peaking my processor
when running. The table has over 2.7 million rows. Is there a better way
to write the sp for better performance or tweak the table? The excution
plan shows TableScan 37%>Filter 56%> Sort 4%>pParallelism/Gather Stream 3%>
Select 0%.
-- TABLE:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Appointment]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[Appointment]
GO
CREATE TABLE [dbo].[Appointment] (
[RecordID] [int] IDENTITY (1, 1) NOT NULL ,
[CPINumber] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[VisitSuffix] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PatientName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[DOB] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Sex] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Race] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SSN] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ApptLocation] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[ResourceCode] [varchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[ResourceGroup] [varchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ApptDate] [datetime] NOT NULL ,
[Duration] [int] NULL ,
[EncounterID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CreatedBy] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CreatedDate] [datetime] NULL ,
[LastChangeBy] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastChangeDate] [datetime] NULL ,
[ApptStatus] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[InterfaceStatus] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[ActivityType] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Comments] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Comments2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Arrived] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[COLD] [datetime] NULL ,
[Comments3] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Comments4] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PatientTypeCode] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FinancialClassCode] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Scraped] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BaseDate] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Appointment] WITH NOCHECK ADD
CONSTRAINT [DF_Appointment_Scraped] DEFAULT (0) FOR [Scraped],
CONSTRAINT [PK_Appointment] PRIMARY KEY NONCLUSTERED
(
[RecordID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_ResourceCode] ON [dbo].[Appointment]([ResourceCode]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_AppointmentCPIDate] ON [dbo].[Appointment]([CPINumber],
[ApptDate]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_Appointment] ON [dbo].[Appointment]([CPINumber],
[VisitSuffix], [ActivityType], [ApptStatus]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
CREATE INDEX [IX_Appointment_ResourceGroup] ON
[dbo].[Appointment]([ResourceGroup]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
-- STORED PROCEDURE::
CREATE Procedure spGetApptsForInterface
As
SELECT *
FROM dbo.Appointment
WHERE ((ResourceCode LIKE 'SHC%' OR
ResourceCode LIKE 'GM%' OR
ResourceCode LIKE 'GI%' or ResourceCode like 'PUL%' or
ResourceCode like 'CAR%' or ResourceCode like 'MED%') AND (ApptDate >=
'04/07/2003') AND (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate,
GETDATE()) > 4)) OR
(((ResourceCode = 'GHCCARAD') or (resourcecode =
'GHCJONES')) AND (ApptDate >= '01/27/2003') AND (InterfaceStatus = 'R') AND
(DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) OR
(((ResourceCode = 'OPCNUTRI') or (resourcecode like
'MON%') or (resourcecode like 'GON%') or (resourcecode like 'LON%') or
(resourcecode like 'END%')) AND (ApptDate >= '07/01/2003') AND
(InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) OR
(((ResourceCode LIKE 'ID%') AND (ApptDate >= '08/12/2003') AND
(InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) or
(((ResourceCode = 'SUREIDTS') or (ResourceCode = 'SUREIDTW') or
(ResourceCode = 'VASLAB1') or (ResourceCode = 'VASLAB2')) AND (ApptDate >=
'12/01/2003') AND (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate,
GETDATE()) > 4)) or
(((ResourceCode = 'PFT') or (ResourceCode = 'PFT2')) AND (ApptDate
>= '1/13/2004') AND (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate,
GETDATE()) > 4)) or
(((ResourceCode like 'CWC%') or (ResourceCode like 'TEL%')) AND
(ApptDate >= '4/13/2004') AND (InterfaceStatus = 'R') AND (DATEDIFF(mi,
CreatedDate, GETDATE()) > 4)) or
(ResourceCode like 'CHEM%' AND ApptDate >= '8/13/2004' AND
InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
(ResourceCode like 'PREOP%' AND ApptDate >= '2/14/2005' AND
InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
(ResourceCode like 'UWHC%' AND ApptDate >= '3/08/2005'
AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
(ResourceCode like 'REN%' AND ApptDate >= '3/08/2005'
AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
(ResourceCode like 'SUR%' AND ApptDate >= '3/08/2005'
AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
(((ResourceCode like 'SON%') or (ResourceCode like
'BON%') or (ResourceCode like 'BMO%')) AND (ApptDate >= '03/14/2005') AND
(InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) or
(((ResourceCode like 'JEI%') or (ResourceCode like
'NEU%') or (ResourceCode like 'ACRC%')) AND (ApptDate >= '04/07/2005') AND
(InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) or
(((ResourceCode like 'DON%') or (ResourceCode like
'HON%') or (ResourceCode like 'JON%') or (ResourceCode like 'UON%') or
(ResourceCode like 'ORT%')) AND (ApptDate >= '04/07/2005') AND
(InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) or
(ResourceCode like 'OON%' AND ApptDate >= '5/23/2005'
AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
(ResourceCode like 'NSG%' AND ApptDate >= '7/27/2005'
AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
(ResourceCode like 'JTS%' AND ApptDate >= '7/27/2005'
AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
(ResourceCode like 'URO%' AND ApptDate >= '7/28/2005'
AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
(ResourceCode like 'LT%' AND ApptDate >= '11/14/2005'
AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
(((ResourceCode = 'CVTOBLE') or (ResourceCode =
'CVANTAK')) AND (ApptDate >= '10/12/2005') AND (InterfaceStatus = 'R') AND
(DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) or
(((ResourceCode like 'INFCH%') or (ResourceCode like
'INFRM%')) AND (ApptDate >= '11/07/2005') AND (InterfaceStatus = 'R') AND
(DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) or
(((ResourceCode like 'MICHAIR%') or (ResourceCode like
'MIEXP%') or (ResourceCode like 'MIROOM%')) AND (ApptDate >= '11/07/2005')
AND (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)))
ORDER BY ApptDate
return
GOThat is a horrendously long WHERE clause and reads like a suspense novel;
could it possibly be simplified?
I've found that such queries can be optimized by wrapping it around a
sub-query that utilizes a simple and selective index search. In doing so,
the remaining WHERE clause is guaranteed to apply against a much smaller
resultset.
Try this: In the example below, I have simply replaced the reference to
table Appointment with a sub-query and aliased it as Appointment. The
remaining query can be left unmodified. If my guess about ApptDate is wrong,
then replace the sub-query condition with something more appropriate.
SELECT
*
FROM
(
select * from dbo.Appointment where ApptDate >= '2003/04/07'
) as Appointment
WHERE
. . .
. . .
. . .
"CD" <mcdye1@.hotmail.REMOVETHIS.com> wrote in message
news:eYmG3WCIGHA.1132@.TK2MSFTNGP10.phx.gbl...
>I am not a programmer but have noticed in this query peaking my processor
>when running. The table has over 2.7 million rows. Is there a better way
>to write the sp for better performance or tweak the table? The excution
>plan shows TableScan 37%>Filter 56%> Sort 4%>pParallelism/Gather Stream 3%>
>Select 0%.
> -- TABLE:
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Appointment]') and OBJECTPROPERTY(id, N'IsUserTable') =
> 1)
> drop table [dbo].[Appointment]
> GO
> CREATE TABLE [dbo].[Appointment] (
> [RecordID] [int] IDENTITY (1, 1) NOT NULL ,
> [CPINumber] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [VisitSuffix] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [PatientName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [DOB] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Sex] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Race] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SSN] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ApptLocation] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [ResourceCode] [varchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [ResourceGroup] [varchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ApptDate] [datetime] NOT NULL ,
> [Duration] [int] NULL ,
> [EncounterID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CreatedBy] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CreatedDate] [datetime] NULL ,
> [LastChangeBy] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [LastChangeDate] [datetime] NULL ,
> [ApptStatus] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [InterfaceStatus] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [ActivityType] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Comments] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Comments2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Arrived] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [COLD] [datetime] NULL ,
> [Comments3] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Comments4] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PatientTypeCode] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [FinancialClassCode] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Scraped] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [BaseDate] [datetime] NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Appointment] WITH NOCHECK ADD
> CONSTRAINT [DF_Appointment_Scraped] DEFAULT (0) FOR [Scraped],
> CONSTRAINT [PK_Appointment] PRIMARY KEY NONCLUSTERED
> (
> [RecordID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [IX_ResourceCode] ON [dbo].[Appointment]([ResourceCode])
> WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [IX_AppointmentCPIDate] ON [dbo].[Appointment]([CPINumber],
> [ApptDate]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [IX_Appointment] ON [dbo].[Appointment]([CPINumber],
> [VisitSuffix], [ActivityType], [ApptStatus]) WITH FILLFACTOR = 90 ON
> [PRIMARY]
> GO
> CREATE INDEX [IX_Appointment_ResourceGroup] ON
> [dbo].[Appointment]([ResourceGroup]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
>
> -- STORED PROCEDURE::
> CREATE Procedure spGetApptsForInterface
> As
> SELECT *
> FROM dbo.Appointment
> WHERE ((ResourceCode LIKE 'SHC%' OR
> ResourceCode LIKE 'GM%' OR
> ResourceCode LIKE 'GI%' or ResourceCode like 'PUL%'
> or ResourceCode like 'CAR%' or ResourceCode like 'MED%') AND (ApptDate >=
> '04/07/2003') AND (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate,
> GETDATE()) > 4)) OR
> (((ResourceCode = 'GHCCARAD') or (resourcecode =
> 'GHCJONES')) AND (ApptDate >= '01/27/2003') AND (InterfaceStatus = 'R')
> AND (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) OR
> (((ResourceCode = 'OPCNUTRI') or (resourcecode like
> 'MON%') or (resourcecode like 'GON%') or (resourcecode like 'LON%') or
> (resourcecode like 'END%')) AND (ApptDate >= '07/01/2003') AND
> (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) OR
> (((ResourceCode LIKE 'ID%') AND (ApptDate >= '08/12/2003') AND
> (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) or
> (((ResourceCode = 'SUREIDTS') or (ResourceCode = 'SUREIDTW') or
> (ResourceCode = 'VASLAB1') or (ResourceCode = 'VASLAB2')) AND (ApptDate >=
> '12/01/2003') AND (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate,
> GETDATE()) > 4)) or
> (((ResourceCode = 'PFT') or (ResourceCode = 'PFT2')) AND (ApptDate
> GETDATE()) > 4)) or
> (((ResourceCode like 'CWC%') or (ResourceCode like 'TEL%')) AND
> (ApptDate >= '4/13/2004') AND (InterfaceStatus = 'R') AND (DATEDIFF(mi,
> CreatedDate, GETDATE()) > 4)) or
> (ResourceCode like 'CHEM%' AND ApptDate >= '8/13/2004' AND
> InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
> (ResourceCode like 'PREOP%' AND ApptDate >= '2/14/2005' AND
> InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
> (ResourceCode like 'UWHC%' AND ApptDate >=
> '3/08/2005' AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate,
> GETDATE()) > 4) or
> (ResourceCode like 'REN%' AND ApptDate >= '3/08/2005'
> AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
> (ResourceCode like 'SUR%' AND ApptDate >= '3/08/2005'
> AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
> (((ResourceCode like 'SON%') or (ResourceCode like
> 'BON%') or (ResourceCode like 'BMO%')) AND (ApptDate >= '03/14/2005') AND
> (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) or
> (((ResourceCode like 'JEI%') or (ResourceCode like
> 'NEU%') or (ResourceCode like 'ACRC%')) AND (ApptDate >= '04/07/2005') AND
> (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) or
> (((ResourceCode like 'DON%') or (ResourceCode like
> 'HON%') or (ResourceCode like 'JON%') or (ResourceCode like 'UON%') or
> (ResourceCode like 'ORT%')) AND (ApptDate >= '04/07/2005') AND
> (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) or
> (ResourceCode like 'OON%' AND ApptDate >= '5/23/2005'
> AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
> (ResourceCode like 'NSG%' AND ApptDate >= '7/27/2005'
> AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
> (ResourceCode like 'JTS%' AND ApptDate >= '7/27/2005'
> AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
> (ResourceCode like 'URO%' AND ApptDate >= '7/28/2005'
> AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
> (ResourceCode like 'LT%' AND ApptDate >= '11/14/2005'
> AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
> (((ResourceCode = 'CVTOBLE') or (ResourceCode =
> 'CVANTAK')) AND (ApptDate >= '10/12/2005') AND (InterfaceStatus = 'R') AND
> (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) or
> (((ResourceCode like 'INFCH%') or (ResourceCode like
> 'INFRM%')) AND (ApptDate >= '11/07/2005') AND (InterfaceStatus = 'R') AND
> (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) or
> (((ResourceCode like 'MICHAIR%') or (ResourceCode like
> 'MIEXP%') or (ResourceCode like 'MIROOM%')) AND (ApptDate >= '11/07/2005')
> AND (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate, GETDATE()) >
> 4)))
>
> ORDER BY ApptDate
> return
> GO
>|||Thanks for the reply. The results are the same as without the subquery.
"JT" <someone@.microsoft.com> wrote in message
news:ug4YLpCIGHA.916@.TK2MSFTNGP10.phx.gbl...
> That is a horrendously long WHERE clause and reads like a suspense novel;
> could it possibly be simplified?
> I've found that such queries can be optimized by wrapping it around a
> sub-query that utilizes a simple and selective index search. In doing so,
> the remaining WHERE clause is guaranteed to apply against a much smaller
> resultset.
> Try this: In the example below, I have simply replaced the reference to
> table Appointment with a sub-query and aliased it as Appointment. The
> remaining query can be left unmodified. If my guess about ApptDate is
> wrong, then replace the sub-query condition with something more
> appropriate.
> SELECT
> *
> FROM
> (
> select * from dbo.Appointment where ApptDate >= '2003/04/07'
> ) as Appointment
> WHERE
> . . .
> . . .
> . . .
>
> "CD" <mcdye1@.hotmail.REMOVETHIS.com> wrote in message
> news:eYmG3WCIGHA.1132@.TK2MSFTNGP10.phx.gbl...
>|||You have no clustered index. That is likely the biggest problem. Pick a
good candidate (appointment date?) and try the query without changes. You'll
need to expericment (and know how the table is usually queried) to pick the
best one. Not something that we can do for you.
Option 1. Update statistics.
Option 2. Compute this value "(DATEDIFF(mi, CreatedDate, GETDATE()) < 4" as
a variable (e.g., now - 4 minutes) and use that in a direct comparison to
CreatedDate. Then, index created date and update your query. I'm not
going to wade through all of this logic, but it appears that this condition
is common to all. If so, you should remove it from each of the condition
sets, making it a condition for the query as a whole. Do likewise for any
other similar conditions.
Option 3. Index on appointment date.
Option 4. Disable parallel exection (with a maxdop hint). Perhaps
parallelism is the problem. Actually, try this first, and cluster, then try
this again if clustering did not help.
Option 5. Index interface status. Also appears to be a common condition.|||The majority of the processing (56%) is by the Filter operator, which scans
the input and returns those rows that satisfy the filter expression. The
additional 37% used by the table scan could also be improved, if you could
find a way to utilize the indexes.
SQL Server's fallback use of the table scan (rather than the more efficient
index scan) is probably the result of the complex filter conditions.
Microsoft attempts to impove the logic of the query optimizer in service
packs, but it will never be perfect. Try some of these techniques to
optimize the WHERE clause:
SQL Server Transact-SQL WHERE Clause
http://www.sql-server-performance.c...t_sql_where.asp
Also, the query optimizer uses table statistics to determine what indexes to
use. If the statistics are out of date, then this can result in a less than
optimal choice or a fallback to a non-indexed table scan.
http://msdn2.microsoft.com/en-us/library/ms187348.aspx
If the above do not help enough, then consider using index hints with the
select statement to force the optimizer to utilze a specfic index.
http://www.sql-server-performance.com/hints.asp
"CD" <mcdye1@.hotmail.REMOVETHIS.com> wrote in message
news:eBR$dzCIGHA.2696@.TK2MSFTNGP14.phx.gbl...
> Thanks for the reply. The results are the same as without the subquery.
> "JT" <someone@.microsoft.com> wrote in message
> news:ug4YLpCIGHA.916@.TK2MSFTNGP10.phx.gbl...
>|||First, the following logic is repeated in every part of this query (every or
clause) and should appear only once.
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
Moving this out of the nested ors MAY allow the database to filter on these
fields up front, thus reducing your dataset before processing all the ORs.
Second, reformatting this miserable code to group things more logically, and
removing many of the redundant () will make ti much mroe readable and thus
easier to understand. I left some redundant () in place for consistency
reasons, but the following should be much easier to follow.
SELECT *
FROM dbo.Appointment
WHERE
(
(
(
ResourceCode LIKE 'SHC%'
or ResourceCode LIKE 'GM%'
or ResourceCode LIKE 'GI%'
or ResourceCode like 'PUL%'
or ResourceCode like 'CAR%'
or ResourceCode like 'MED%'
)
AND ApptDate >= '04/07/2003'
)
or
(
(
ResourceCode = 'GHCCARAD'
or resourcecode = 'GHCJONES'
)
AND ApptDate >= '01/27/2003'
)
or
(
(
ResourceCode = 'OPCNUTRI'
or resourcecode like 'MON%'
or resourcecode like 'GON%'
or resourcecode like 'LON%'
or resourcecode like 'END%'
)
AND ApptDate >= '07/01/2003'
)
or
(
(
ResourceCode LIKE 'ID%'
)
AND ApptDate >= '08/12/2003'
)
or
(
(
ResourceCode = 'SUREIDTS'
or ResourceCode = 'SUREIDTW'
or ResourceCode = 'VASLAB1'
or ResourceCode = 'VASLAB2'
)
AND ApptDate >= '12/01/2003'
)
or
(
(
ResourceCode = 'PFT'
or ResourceCode = 'PFT2'
)
AND ApptDate >= '1/13/2004'
)
or
(
(
ResourceCode like 'CWC%'
or ResourceCode like 'TEL%'
)
AND ApptDate >= '4/13/2004'
)
or
(
(
ResourceCode like 'CHEM%'
)
AND ApptDate >= '8/13/2004'
)
or
(
(
ResourceCode like 'PREOP%'
)
AND ApptDate >= '2/14/2005'
)
or
(
(
ResourceCode like 'UWHC%'
or ResourceCode like 'REN%'
or ResourceCode like 'SUR%'
)
AND ApptDate >= '3/08/2005'
)
or
(
(
ResourceCode like 'SON%'
or ResourceCode like 'BON%'
or ResourceCode like 'BMO%'
)
AND ApptDate >= '03/14/2005'
)
or
(
(
ResourceCode like 'JEI%'
or ResourceCode like 'NEU%'
or ResourceCode like 'ACRC%'
or ResourceCode like 'DON%'
or ResourceCode like 'HON%'
or ResourceCode like 'JON%'
or ResourceCode like 'UON%'
or ResourceCode like 'ORT%'
)
AND ApptDate >= '04/07/2005'
)
or
(
(
ResourceCode like 'OON%'
)
AND ApptDate >= '5/23/2005'
)
or
(
(
ResourceCode like 'NSG%'
or ResourceCode like 'JTS%'
)
AND ApptDate >= '7/27/2005'
)
or
(
(
ResourceCode like 'URO%'
)
AND ApptDate >= '7/28/2005'
)
or
(
(
ResourceCode like 'LT%'
)
AND ApptDate >= '11/14/2005'
)
or
(
(
ResourceCode = 'CVTOBLE'
or ResourceCode = 'CVANTAK'
)
AND ApptDate >= '10/12/2005'
)
or
(
(
ResourceCode like 'INFCH%'
or ResourceCode like 'INFRM%'
or ResourceCode like 'MICHAIR%'
or ResourceCode like 'MIEXP%'
or ResourceCode like 'MIROOM%'
)
AND ApptDate >= '11/07/2005'
)
)
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
"CD" <mcdye1@.hotmail.REMOVETHIS.com> wrote in message
news:eYmG3WCIGHA.1132@.TK2MSFTNGP10.phx.gbl...
> I am not a programmer but have noticed in this query peaking my processor
> when running. The table has over 2.7 million rows. Is there a better way
> to write the sp for better performance or tweak the table? The excution
> plan shows TableScan 37%>Filter 56%> Sort 4%>pParallelism/Gather Stream
3%>
> Select 0%.
> -- TABLE:
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Appointment]') and OBJECTPROPERTY(id, N'IsUserTable') =
> 1)
> drop table [dbo].[Appointment]
> GO
> CREATE TABLE [dbo].[Appointment] (
> [RecordID] [int] IDENTITY (1, 1) NOT NULL ,
> [CPINumber] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [VisitSuffix] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
> [PatientName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
> ,
> [DOB] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Sex] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Race] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SSN] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ApptLocation] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
> ,
> [ResourceCode] [varchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
> ,
> [ResourceGroup] [varchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [ApptDate] [datetime] NOT NULL ,
> [Duration] [int] NULL ,
> [EncounterID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CreatedBy] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CreatedDate] [datetime] NULL ,
> [LastChangeBy] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [LastChangeDate] [datetime] NULL ,
> [ApptStatus] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
> [InterfaceStatus] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
> ,
> [ActivityType] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Comments] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Comments2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Arrived] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [COLD] [datetime] NULL ,
> [Comments3] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Comments4] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PatientTypeCode] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [FinancialClassCode] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Scraped] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [BaseDate] [datetime] NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Appointment] WITH NOCHECK ADD
> CONSTRAINT [DF_Appointment_Scraped] DEFAULT (0) FOR [Scraped],
> CONSTRAINT [PK_Appointment] PRIMARY KEY NONCLUSTERED
> (
> [RecordID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [IX_ResourceCode] ON [dbo].[Appointment]([ResourceCode])
WITH
> FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [IX_AppointmentCPIDate] ON [dbo].[Appointment]([CPINumber],
> [ApptDate]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [IX_Appointment] ON [dbo].[Appointment]([CPINumber],
> [VisitSuffix], [ActivityType], [ApptStatus]) WITH FILLFACTOR = 90 ON
> [PRIMARY]
> GO
> CREATE INDEX [IX_Appointment_ResourceGroup] ON
> [dbo].[Appointment]([ResourceGroup]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
>
> -- STORED PROCEDURE::
> CREATE Procedure spGetApptsForInterface
> As
> SELECT *
> FROM dbo.Appointment
> WHERE ((ResourceCode LIKE 'SHC%' OR
> ResourceCode LIKE 'GM%' OR
> ResourceCode LIKE 'GI%' or ResourceCode like 'PUL%'
or
> ResourceCode like 'CAR%' or ResourceCode like 'MED%') AND (ApptDate >=
> '04/07/2003') AND (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate,
> GETDATE()) > 4)) OR
> (((ResourceCode = 'GHCCARAD') or (resourcecode =
> 'GHCJONES')) AND (ApptDate >= '01/27/2003') AND (InterfaceStatus = 'R')
AND
> (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) OR
> (((ResourceCode = 'OPCNUTRI') or (resourcecode like
> 'MON%') or (resourcecode like 'GON%') or (resourcecode like 'LON%') or
> (resourcecode like 'END%')) AND (ApptDate >= '07/01/2003') AND
> (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) OR
> (((ResourceCode LIKE 'ID%') AND (ApptDate >= '08/12/2003') AND
> (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) or
> (((ResourceCode = 'SUREIDTS') or (ResourceCode = 'SUREIDTW') or
> (ResourceCode = 'VASLAB1') or (ResourceCode = 'VASLAB2')) AND (ApptDate >=
> '12/01/2003') AND (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate,
> GETDATE()) > 4)) or
> (((ResourceCode = 'PFT') or (ResourceCode = 'PFT2')) AND
(ApptDate
CreatedDate,
> GETDATE()) > 4)) or
> (((ResourceCode like 'CWC%') or (ResourceCode like 'TEL%')) AND
> (ApptDate >= '4/13/2004') AND (InterfaceStatus = 'R') AND (DATEDIFF(mi,
> CreatedDate, GETDATE()) > 4)) or
> (ResourceCode like 'CHEM%' AND ApptDate >= '8/13/2004' AND
> InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
> (ResourceCode like 'PREOP%' AND ApptDate >= '2/14/2005' AND
> InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
> (ResourceCode like 'UWHC%' AND ApptDate >=
'3/08/2005'
> AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
> (ResourceCode like 'REN%' AND ApptDate >=
'3/08/2005'
> AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
> (ResourceCode like 'SUR%' AND ApptDate >=
'3/08/2005'
> AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
> (((ResourceCode like 'SON%') or (ResourceCode like
> 'BON%') or (ResourceCode like 'BMO%')) AND (ApptDate >= '03/14/2005') AND
> (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) or
> (((ResourceCode like 'JEI%') or (ResourceCode like
> 'NEU%') or (ResourceCode like 'ACRC%')) AND (ApptDate >= '04/07/2005') AND
> (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) or
> (((ResourceCode like 'DON%') or (ResourceCode like
> 'HON%') or (ResourceCode like 'JON%') or (ResourceCode like 'UON%') or
> (ResourceCode like 'ORT%')) AND (ApptDate >= '04/07/2005') AND
> (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) or
> (ResourceCode like 'OON%' AND ApptDate >=
'5/23/2005'
> AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
> (ResourceCode like 'NSG%' AND ApptDate >=
'7/27/2005'
> AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
> (ResourceCode like 'JTS%' AND ApptDate >=
'7/27/2005'
> AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
> (ResourceCode like 'URO%' AND ApptDate >=
'7/28/2005'
> AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
> (ResourceCode like 'LT%' AND ApptDate >=
'11/14/2005'
> AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
> (((ResourceCode = 'CVTOBLE') or (ResourceCode =
> 'CVANTAK')) AND (ApptDate >= '10/12/2005') AND (InterfaceStatus = 'R') AND
> (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) or
> (((ResourceCode like 'INFCH%') or (ResourceCode like
> 'INFRM%')) AND (ApptDate >= '11/07/2005') AND (InterfaceStatus = 'R') AND
> (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) or
> (((ResourceCode like 'MICHAIR%') or (ResourceCode
like
> 'MIEXP%') or (ResourceCode like 'MIROOM%')) AND (ApptDate >= '11/07/2005')
> AND (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate, GETDATE()) >
4)))
>
> ORDER BY ApptDate
> return
> GO
>|||I should note that I consolidate some of the resource code comparisons where
the dates were the same.
Also, you might try changing all those OR clauses to unions, although I am
not certain if SQL Server will handle them the same or not. The idea is
that the unions will be ~ 12 independent queries which may individually
process quite fast, as compared to the ORs which MAY require the database to
process every single row in order to evaluate all conditions.
Not fully understanding the SQL server tuning engine, I cant be sure if
either of these ideas will make any difference at all. Adding the indexes
as suggested by others may make a much bigger difference wihtout changing
the code.
Just in case, here is the SQL as a bunch of unions...
SELECT *
FROM dbo.Appointment
WHERE
(
ResourceCode LIKE 'SHC%'
or ResourceCode LIKE 'GM%'
or ResourceCode LIKE 'GI%'
or ResourceCode like 'PUL%'
or ResourceCode like 'CAR%'
or ResourceCode like 'MED%'
)
AND ApptDate >= '04/07/2003'
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
Union all
SELECT *
FROM dbo.Appointment
WHERE
(
ResourceCode = 'GHCCARAD'
or resourcecode = 'GHCJONES'
)
AND ApptDate >= '01/27/2003'
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
Union all
SELECT *
FROM dbo.Appointment
WHERE
(
ResourceCode = 'OPCNUTRI'
or resourcecode like 'MON%'
or resourcecode like 'GON%'
or resourcecode like 'LON%'
or resourcecode like 'END%'
)
AND ApptDate >= '07/01/2003'
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
Union all
SELECT *
FROM dbo.Appointment
WHERE
(
ResourceCode LIKE 'ID%'
)
AND ApptDate >= '08/12/2003'
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
Union all
SELECT *
FROM dbo.Appointment
WHERE
(
ResourceCode = 'SUREIDTS'
or ResourceCode = 'SUREIDTW'
or ResourceCode = 'VASLAB1'
or ResourceCode = 'VASLAB2'
)
AND ApptDate >= '12/01/2003'
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
Union all
SELECT *
FROM dbo.Appointment
WHERE
(
ResourceCode = 'PFT'
or ResourceCode = 'PFT2'
)
AND ApptDate >= '1/13/2004'
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
Union all
SELECT *
FROM dbo.Appointment
WHERE
(
ResourceCode like 'CWC%'
or ResourceCode like 'TEL%'
)
AND ApptDate >= '4/13/2004'
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
Union all
SELECT *
FROM dbo.Appointment
WHERE
(
ResourceCode like 'CHEM%'
)
AND ApptDate >= '8/13/2004'
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
Union all
SELECT *
FROM dbo.Appointment
WHERE
(
ResourceCode like 'PREOP%'
)
AND ApptDate >= '2/14/2005'
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
Union all
SELECT *
FROM dbo.Appointment
WHERE
(
ResourceCode like 'UWHC%'
or ResourceCode like 'REN%'
or ResourceCode like 'SUR%'
)
AND ApptDate >= '3/08/2005'
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
Union all
SELECT *
FROM dbo.Appointment
WHERE
(
ResourceCode like 'SON%'
or ResourceCode like 'BON%'
or ResourceCode like 'BMO%'
)
AND ApptDate >= '03/14/2005'
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
Union all
SELECT *
FROM dbo.Appointment
WHERE
(
ResourceCode like 'JEI%'
or ResourceCode like 'NEU%'
or ResourceCode like 'ACRC%'
or ResourceCode like 'DON%'
or ResourceCode like 'HON%'
or ResourceCode like 'JON%'
or ResourceCode like 'UON%'
or ResourceCode like 'ORT%'
)
AND ApptDate >= '04/07/2005'
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
Union all
SELECT *
FROM dbo.Appointment
WHERE
(
ResourceCode like 'OON%'
)
AND ApptDate >= '5/23/2005'
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
Union all
SELECT *
FROM dbo.Appointment
WHERE
(
ResourceCode like 'NSG%'
or ResourceCode like 'JTS%'
)
AND ApptDate >= '7/27/2005'
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
Union all
SELECT *
FROM dbo.Appointment
WHERE
(
ResourceCode like 'URO%'
)
AND ApptDate >= '7/28/2005'
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
Union all
SELECT *
FROM dbo.Appointment
WHERE
(
ResourceCode like 'LT%'
)
AND ApptDate >= '11/14/2005'
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
Union all
SELECT *
FROM dbo.Appointment
WHERE
(
ResourceCode = 'CVTOBLE'
or ResourceCode = 'CVANTAK'
)
AND ApptDate >= '10/12/2005'
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
Union all
SELECT *
FROM dbo.Appointment
WHERE
(
ResourceCode like 'INFCH%'
or ResourceCode like 'INFRM%'
or ResourceCode like 'MICHAIR%'
or ResourceCode like 'MIEXP%'
or ResourceCode like 'MIROOM%'
)
AND ApptDate >= '11/07/2005'
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:%23y7yjkDIGHA.2912@.tk2msftngp13.phx.gbl...
> First, the following logic is repeated in every part of this query (every
or
> clause) and should appear only once.
> AND InterfaceStatus = 'R'
> AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
> Moving this out of the nested ors MAY allow the database to filter on
these
> fields up front, thus reducing your dataset before processing all the ORs.
> Second, reformatting this miserable code to group things more logically,
and
> removing many of the redundant () will make ti much mroe readable and thus
> easier to understand. I left some redundant () in place for consistency
> reasons, but the following should be much easier to follow.
> SELECT *
> FROM dbo.Appointment
> WHERE
> (
> (
> (
> ResourceCode LIKE 'SHC%'
> or ResourceCode LIKE 'GM%'
> or ResourceCode LIKE 'GI%'
> or ResourceCode like 'PUL%'
> or ResourceCode like 'CAR%'
> or ResourceCode like 'MED%'
> )
> AND ApptDate >= '04/07/2003'
> )
> or
> (
> (
> ResourceCode = 'GHCCARAD'
> or resourcecode = 'GHCJONES'
> )
> AND ApptDate >= '01/27/2003'
> )
> or
> (
> (
> ResourceCode = 'OPCNUTRI'
> or resourcecode like 'MON%'
> or resourcecode like 'GON%'
> or resourcecode like 'LON%'
> or resourcecode like 'END%'
> )
> AND ApptDate >= '07/01/2003'
> )
> or
> (
> (
> ResourceCode LIKE 'ID%'
> )
> AND ApptDate >= '08/12/2003'
> )
> or
> (
> (
> ResourceCode = 'SUREIDTS'
> or ResourceCode = 'SUREIDTW'
> or ResourceCode = 'VASLAB1'
> or ResourceCode = 'VASLAB2'
> )
> AND ApptDate >= '12/01/2003'
> )
> or
> (
> (
> ResourceCode = 'PFT'
> or ResourceCode = 'PFT2'
> )
> AND ApptDate >= '1/13/2004'
> )
> or
> (
> (
> ResourceCode like 'CWC%'
> or ResourceCode like 'TEL%'
> )
> AND ApptDate >= '4/13/2004'
> )
> or
> (
> (
> ResourceCode like 'CHEM%'
> )
> AND ApptDate >= '8/13/2004'
> )
> or
> (
> (
> ResourceCode like 'PREOP%'
> )
> AND ApptDate >= '2/14/2005'
> )
> or
> (
> (
> ResourceCode like 'UWHC%'
> or ResourceCode like 'REN%'
> or ResourceCode like 'SUR%'
> )
> AND ApptDate >= '3/08/2005'
> )
> or
> (
> (
> ResourceCode like 'SON%'
> or ResourceCode like 'BON%'
> or ResourceCode like 'BMO%'
> )
> AND ApptDate >= '03/14/2005'
> )
> or
> (
> (
> ResourceCode like 'JEI%'
> or ResourceCode like 'NEU%'
> or ResourceCode like 'ACRC%'
> or ResourceCode like 'DON%'
> or ResourceCode like 'HON%'
> or ResourceCode like 'JON%'
> or ResourceCode like 'UON%'
> or ResourceCode like 'ORT%'
> )
> AND ApptDate >= '04/07/2005'
> )
> or
> (
> (
> ResourceCode like 'OON%'
> )
> AND ApptDate >= '5/23/2005'
> )
> or
> (
> (
> ResourceCode like 'NSG%'
> or ResourceCode like 'JTS%'
> )
> AND ApptDate >= '7/27/2005'
> )
> or
> (
> (
> ResourceCode like 'URO%'
> )
> AND ApptDate >= '7/28/2005'
> )
> or
> (
> (
> ResourceCode like 'LT%'
> )
> AND ApptDate >= '11/14/2005'
> )
> or
> (
> (
> ResourceCode = 'CVTOBLE'
> or ResourceCode = 'CVANTAK'
> )
> AND ApptDate >= '10/12/2005'
> )
> or
> (
> (
> ResourceCode like 'INFCH%'
> or ResourceCode like 'INFRM%'
> or ResourceCode like 'MICHAIR%'
> or ResourceCode like 'MIEXP%'
> or ResourceCode like 'MIROOM%'
> )
> AND ApptDate >= '11/07/2005'
> )
> )
> AND InterfaceStatus = 'R'
> AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
>
> "CD" <mcdye1@.hotmail.REMOVETHIS.com> wrote in message
> news:eYmG3WCIGHA.1132@.TK2MSFTNGP10.phx.gbl...
processor
way
> 3%>
=
,
NULL
> ,
> NULL
> NULL
> NULL
NULL
> ,
,
,
NULL
> ,
> NULL
,
NULL
> ,
> WITH
[dbo]. [Appointment]([CPINumber],[color=darkred
]
'PUL%'
> or
> AND
like
OR
or
>=
> (ApptDate
> CreatedDate,
> '3/08/2005'
or
> '3/08/2005'
or
> '3/08/2005'
or
AND
or
AND
or
or
> '5/23/2005'
or
> '7/27/2005'
or
> '7/27/2005'
or
> '7/28/2005'
or
> '11/14/2005'
or
AND
like
AND
> like
'11/07/2005')
> 4)))
>sql
when running. The table has over 2.7 million rows. Is there a better way
to write the sp for better performance or tweak the table? The excution
plan shows TableScan 37%>Filter 56%> Sort 4%>pParallelism/Gather Stream 3%>
Select 0%.
-- TABLE:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Appointment]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[Appointment]
GO
CREATE TABLE [dbo].[Appointment] (
[RecordID] [int] IDENTITY (1, 1) NOT NULL ,
[CPINumber] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[VisitSuffix] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PatientName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[DOB] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Sex] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Race] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SSN] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ApptLocation] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[ResourceCode] [varchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[ResourceGroup] [varchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ApptDate] [datetime] NOT NULL ,
[Duration] [int] NULL ,
[EncounterID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CreatedBy] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CreatedDate] [datetime] NULL ,
[LastChangeBy] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastChangeDate] [datetime] NULL ,
[ApptStatus] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[InterfaceStatus] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[ActivityType] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Comments] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Comments2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Arrived] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[COLD] [datetime] NULL ,
[Comments3] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Comments4] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PatientTypeCode] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FinancialClassCode] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Scraped] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BaseDate] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Appointment] WITH NOCHECK ADD
CONSTRAINT [DF_Appointment_Scraped] DEFAULT (0) FOR [Scraped],
CONSTRAINT [PK_Appointment] PRIMARY KEY NONCLUSTERED
(
[RecordID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_ResourceCode] ON [dbo].[Appointment]([ResourceCode]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_AppointmentCPIDate] ON [dbo].[Appointment]([CPINumber],
[ApptDate]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_Appointment] ON [dbo].[Appointment]([CPINumber],
[VisitSuffix], [ActivityType], [ApptStatus]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
CREATE INDEX [IX_Appointment_ResourceGroup] ON
[dbo].[Appointment]([ResourceGroup]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
-- STORED PROCEDURE::
CREATE Procedure spGetApptsForInterface
As
SELECT *
FROM dbo.Appointment
WHERE ((ResourceCode LIKE 'SHC%' OR
ResourceCode LIKE 'GM%' OR
ResourceCode LIKE 'GI%' or ResourceCode like 'PUL%' or
ResourceCode like 'CAR%' or ResourceCode like 'MED%') AND (ApptDate >=
'04/07/2003') AND (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate,
GETDATE()) > 4)) OR
(((ResourceCode = 'GHCCARAD') or (resourcecode =
'GHCJONES')) AND (ApptDate >= '01/27/2003') AND (InterfaceStatus = 'R') AND
(DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) OR
(((ResourceCode = 'OPCNUTRI') or (resourcecode like
'MON%') or (resourcecode like 'GON%') or (resourcecode like 'LON%') or
(resourcecode like 'END%')) AND (ApptDate >= '07/01/2003') AND
(InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) OR
(((ResourceCode LIKE 'ID%') AND (ApptDate >= '08/12/2003') AND
(InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) or
(((ResourceCode = 'SUREIDTS') or (ResourceCode = 'SUREIDTW') or
(ResourceCode = 'VASLAB1') or (ResourceCode = 'VASLAB2')) AND (ApptDate >=
'12/01/2003') AND (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate,
GETDATE()) > 4)) or
(((ResourceCode = 'PFT') or (ResourceCode = 'PFT2')) AND (ApptDate
>= '1/13/2004') AND (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate,
GETDATE()) > 4)) or
(((ResourceCode like 'CWC%') or (ResourceCode like 'TEL%')) AND
(ApptDate >= '4/13/2004') AND (InterfaceStatus = 'R') AND (DATEDIFF(mi,
CreatedDate, GETDATE()) > 4)) or
(ResourceCode like 'CHEM%' AND ApptDate >= '8/13/2004' AND
InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
(ResourceCode like 'PREOP%' AND ApptDate >= '2/14/2005' AND
InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
(ResourceCode like 'UWHC%' AND ApptDate >= '3/08/2005'
AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
(ResourceCode like 'REN%' AND ApptDate >= '3/08/2005'
AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
(ResourceCode like 'SUR%' AND ApptDate >= '3/08/2005'
AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
(((ResourceCode like 'SON%') or (ResourceCode like
'BON%') or (ResourceCode like 'BMO%')) AND (ApptDate >= '03/14/2005') AND
(InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) or
(((ResourceCode like 'JEI%') or (ResourceCode like
'NEU%') or (ResourceCode like 'ACRC%')) AND (ApptDate >= '04/07/2005') AND
(InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) or
(((ResourceCode like 'DON%') or (ResourceCode like
'HON%') or (ResourceCode like 'JON%') or (ResourceCode like 'UON%') or
(ResourceCode like 'ORT%')) AND (ApptDate >= '04/07/2005') AND
(InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) or
(ResourceCode like 'OON%' AND ApptDate >= '5/23/2005'
AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
(ResourceCode like 'NSG%' AND ApptDate >= '7/27/2005'
AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
(ResourceCode like 'JTS%' AND ApptDate >= '7/27/2005'
AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
(ResourceCode like 'URO%' AND ApptDate >= '7/28/2005'
AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
(ResourceCode like 'LT%' AND ApptDate >= '11/14/2005'
AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
(((ResourceCode = 'CVTOBLE') or (ResourceCode =
'CVANTAK')) AND (ApptDate >= '10/12/2005') AND (InterfaceStatus = 'R') AND
(DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) or
(((ResourceCode like 'INFCH%') or (ResourceCode like
'INFRM%')) AND (ApptDate >= '11/07/2005') AND (InterfaceStatus = 'R') AND
(DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) or
(((ResourceCode like 'MICHAIR%') or (ResourceCode like
'MIEXP%') or (ResourceCode like 'MIROOM%')) AND (ApptDate >= '11/07/2005')
AND (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)))
ORDER BY ApptDate
return
GOThat is a horrendously long WHERE clause and reads like a suspense novel;
could it possibly be simplified?
I've found that such queries can be optimized by wrapping it around a
sub-query that utilizes a simple and selective index search. In doing so,
the remaining WHERE clause is guaranteed to apply against a much smaller
resultset.
Try this: In the example below, I have simply replaced the reference to
table Appointment with a sub-query and aliased it as Appointment. The
remaining query can be left unmodified. If my guess about ApptDate is wrong,
then replace the sub-query condition with something more appropriate.
SELECT
*
FROM
(
select * from dbo.Appointment where ApptDate >= '2003/04/07'
) as Appointment
WHERE
. . .
. . .
. . .
"CD" <mcdye1@.hotmail.REMOVETHIS.com> wrote in message
news:eYmG3WCIGHA.1132@.TK2MSFTNGP10.phx.gbl...
>I am not a programmer but have noticed in this query peaking my processor
>when running. The table has over 2.7 million rows. Is there a better way
>to write the sp for better performance or tweak the table? The excution
>plan shows TableScan 37%>Filter 56%> Sort 4%>pParallelism/Gather Stream 3%>
>Select 0%.
> -- TABLE:
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Appointment]') and OBJECTPROPERTY(id, N'IsUserTable') =
> 1)
> drop table [dbo].[Appointment]
> GO
> CREATE TABLE [dbo].[Appointment] (
> [RecordID] [int] IDENTITY (1, 1) NOT NULL ,
> [CPINumber] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [VisitSuffix] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [PatientName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [DOB] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Sex] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Race] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SSN] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ApptLocation] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [ResourceCode] [varchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [ResourceGroup] [varchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ApptDate] [datetime] NOT NULL ,
> [Duration] [int] NULL ,
> [EncounterID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CreatedBy] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CreatedDate] [datetime] NULL ,
> [LastChangeBy] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [LastChangeDate] [datetime] NULL ,
> [ApptStatus] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [InterfaceStatus] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [ActivityType] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Comments] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Comments2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Arrived] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [COLD] [datetime] NULL ,
> [Comments3] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Comments4] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PatientTypeCode] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [FinancialClassCode] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Scraped] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [BaseDate] [datetime] NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Appointment] WITH NOCHECK ADD
> CONSTRAINT [DF_Appointment_Scraped] DEFAULT (0) FOR [Scraped],
> CONSTRAINT [PK_Appointment] PRIMARY KEY NONCLUSTERED
> (
> [RecordID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [IX_ResourceCode] ON [dbo].[Appointment]([ResourceCode])
> WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [IX_AppointmentCPIDate] ON [dbo].[Appointment]([CPINumber],
> [ApptDate]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [IX_Appointment] ON [dbo].[Appointment]([CPINumber],
> [VisitSuffix], [ActivityType], [ApptStatus]) WITH FILLFACTOR = 90 ON
> [PRIMARY]
> GO
> CREATE INDEX [IX_Appointment_ResourceGroup] ON
> [dbo].[Appointment]([ResourceGroup]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
>
> -- STORED PROCEDURE::
> CREATE Procedure spGetApptsForInterface
> As
> SELECT *
> FROM dbo.Appointment
> WHERE ((ResourceCode LIKE 'SHC%' OR
> ResourceCode LIKE 'GM%' OR
> ResourceCode LIKE 'GI%' or ResourceCode like 'PUL%'
> or ResourceCode like 'CAR%' or ResourceCode like 'MED%') AND (ApptDate >=
> '04/07/2003') AND (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate,
> GETDATE()) > 4)) OR
> (((ResourceCode = 'GHCCARAD') or (resourcecode =
> 'GHCJONES')) AND (ApptDate >= '01/27/2003') AND (InterfaceStatus = 'R')
> AND (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) OR
> (((ResourceCode = 'OPCNUTRI') or (resourcecode like
> 'MON%') or (resourcecode like 'GON%') or (resourcecode like 'LON%') or
> (resourcecode like 'END%')) AND (ApptDate >= '07/01/2003') AND
> (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) OR
> (((ResourceCode LIKE 'ID%') AND (ApptDate >= '08/12/2003') AND
> (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) or
> (((ResourceCode = 'SUREIDTS') or (ResourceCode = 'SUREIDTW') or
> (ResourceCode = 'VASLAB1') or (ResourceCode = 'VASLAB2')) AND (ApptDate >=
> '12/01/2003') AND (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate,
> GETDATE()) > 4)) or
> (((ResourceCode = 'PFT') or (ResourceCode = 'PFT2')) AND (ApptDate
> GETDATE()) > 4)) or
> (((ResourceCode like 'CWC%') or (ResourceCode like 'TEL%')) AND
> (ApptDate >= '4/13/2004') AND (InterfaceStatus = 'R') AND (DATEDIFF(mi,
> CreatedDate, GETDATE()) > 4)) or
> (ResourceCode like 'CHEM%' AND ApptDate >= '8/13/2004' AND
> InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
> (ResourceCode like 'PREOP%' AND ApptDate >= '2/14/2005' AND
> InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
> (ResourceCode like 'UWHC%' AND ApptDate >=
> '3/08/2005' AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate,
> GETDATE()) > 4) or
> (ResourceCode like 'REN%' AND ApptDate >= '3/08/2005'
> AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
> (ResourceCode like 'SUR%' AND ApptDate >= '3/08/2005'
> AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
> (((ResourceCode like 'SON%') or (ResourceCode like
> 'BON%') or (ResourceCode like 'BMO%')) AND (ApptDate >= '03/14/2005') AND
> (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) or
> (((ResourceCode like 'JEI%') or (ResourceCode like
> 'NEU%') or (ResourceCode like 'ACRC%')) AND (ApptDate >= '04/07/2005') AND
> (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) or
> (((ResourceCode like 'DON%') or (ResourceCode like
> 'HON%') or (ResourceCode like 'JON%') or (ResourceCode like 'UON%') or
> (ResourceCode like 'ORT%')) AND (ApptDate >= '04/07/2005') AND
> (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) or
> (ResourceCode like 'OON%' AND ApptDate >= '5/23/2005'
> AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
> (ResourceCode like 'NSG%' AND ApptDate >= '7/27/2005'
> AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
> (ResourceCode like 'JTS%' AND ApptDate >= '7/27/2005'
> AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
> (ResourceCode like 'URO%' AND ApptDate >= '7/28/2005'
> AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
> (ResourceCode like 'LT%' AND ApptDate >= '11/14/2005'
> AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
> (((ResourceCode = 'CVTOBLE') or (ResourceCode =
> 'CVANTAK')) AND (ApptDate >= '10/12/2005') AND (InterfaceStatus = 'R') AND
> (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) or
> (((ResourceCode like 'INFCH%') or (ResourceCode like
> 'INFRM%')) AND (ApptDate >= '11/07/2005') AND (InterfaceStatus = 'R') AND
> (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) or
> (((ResourceCode like 'MICHAIR%') or (ResourceCode like
> 'MIEXP%') or (ResourceCode like 'MIROOM%')) AND (ApptDate >= '11/07/2005')
> AND (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate, GETDATE()) >
> 4)))
>
> ORDER BY ApptDate
> return
> GO
>|||Thanks for the reply. The results are the same as without the subquery.
"JT" <someone@.microsoft.com> wrote in message
news:ug4YLpCIGHA.916@.TK2MSFTNGP10.phx.gbl...
> That is a horrendously long WHERE clause and reads like a suspense novel;
> could it possibly be simplified?
> I've found that such queries can be optimized by wrapping it around a
> sub-query that utilizes a simple and selective index search. In doing so,
> the remaining WHERE clause is guaranteed to apply against a much smaller
> resultset.
> Try this: In the example below, I have simply replaced the reference to
> table Appointment with a sub-query and aliased it as Appointment. The
> remaining query can be left unmodified. If my guess about ApptDate is
> wrong, then replace the sub-query condition with something more
> appropriate.
> SELECT
> *
> FROM
> (
> select * from dbo.Appointment where ApptDate >= '2003/04/07'
> ) as Appointment
> WHERE
> . . .
> . . .
> . . .
>
> "CD" <mcdye1@.hotmail.REMOVETHIS.com> wrote in message
> news:eYmG3WCIGHA.1132@.TK2MSFTNGP10.phx.gbl...
>|||You have no clustered index. That is likely the biggest problem. Pick a
good candidate (appointment date?) and try the query without changes. You'll
need to expericment (and know how the table is usually queried) to pick the
best one. Not something that we can do for you.
Option 1. Update statistics.
Option 2. Compute this value "(DATEDIFF(mi, CreatedDate, GETDATE()) < 4" as
a variable (e.g., now - 4 minutes) and use that in a direct comparison to
CreatedDate. Then, index created date and update your query. I'm not
going to wade through all of this logic, but it appears that this condition
is common to all. If so, you should remove it from each of the condition
sets, making it a condition for the query as a whole. Do likewise for any
other similar conditions.
Option 3. Index on appointment date.
Option 4. Disable parallel exection (with a maxdop hint). Perhaps
parallelism is the problem. Actually, try this first, and cluster, then try
this again if clustering did not help.
Option 5. Index interface status. Also appears to be a common condition.|||The majority of the processing (56%) is by the Filter operator, which scans
the input and returns those rows that satisfy the filter expression. The
additional 37% used by the table scan could also be improved, if you could
find a way to utilize the indexes.
SQL Server's fallback use of the table scan (rather than the more efficient
index scan) is probably the result of the complex filter conditions.
Microsoft attempts to impove the logic of the query optimizer in service
packs, but it will never be perfect. Try some of these techniques to
optimize the WHERE clause:
SQL Server Transact-SQL WHERE Clause
http://www.sql-server-performance.c...t_sql_where.asp
Also, the query optimizer uses table statistics to determine what indexes to
use. If the statistics are out of date, then this can result in a less than
optimal choice or a fallback to a non-indexed table scan.
http://msdn2.microsoft.com/en-us/library/ms187348.aspx
If the above do not help enough, then consider using index hints with the
select statement to force the optimizer to utilze a specfic index.
http://www.sql-server-performance.com/hints.asp
"CD" <mcdye1@.hotmail.REMOVETHIS.com> wrote in message
news:eBR$dzCIGHA.2696@.TK2MSFTNGP14.phx.gbl...
> Thanks for the reply. The results are the same as without the subquery.
> "JT" <someone@.microsoft.com> wrote in message
> news:ug4YLpCIGHA.916@.TK2MSFTNGP10.phx.gbl...
>|||First, the following logic is repeated in every part of this query (every or
clause) and should appear only once.
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
Moving this out of the nested ors MAY allow the database to filter on these
fields up front, thus reducing your dataset before processing all the ORs.
Second, reformatting this miserable code to group things more logically, and
removing many of the redundant () will make ti much mroe readable and thus
easier to understand. I left some redundant () in place for consistency
reasons, but the following should be much easier to follow.
SELECT *
FROM dbo.Appointment
WHERE
(
(
(
ResourceCode LIKE 'SHC%'
or ResourceCode LIKE 'GM%'
or ResourceCode LIKE 'GI%'
or ResourceCode like 'PUL%'
or ResourceCode like 'CAR%'
or ResourceCode like 'MED%'
)
AND ApptDate >= '04/07/2003'
)
or
(
(
ResourceCode = 'GHCCARAD'
or resourcecode = 'GHCJONES'
)
AND ApptDate >= '01/27/2003'
)
or
(
(
ResourceCode = 'OPCNUTRI'
or resourcecode like 'MON%'
or resourcecode like 'GON%'
or resourcecode like 'LON%'
or resourcecode like 'END%'
)
AND ApptDate >= '07/01/2003'
)
or
(
(
ResourceCode LIKE 'ID%'
)
AND ApptDate >= '08/12/2003'
)
or
(
(
ResourceCode = 'SUREIDTS'
or ResourceCode = 'SUREIDTW'
or ResourceCode = 'VASLAB1'
or ResourceCode = 'VASLAB2'
)
AND ApptDate >= '12/01/2003'
)
or
(
(
ResourceCode = 'PFT'
or ResourceCode = 'PFT2'
)
AND ApptDate >= '1/13/2004'
)
or
(
(
ResourceCode like 'CWC%'
or ResourceCode like 'TEL%'
)
AND ApptDate >= '4/13/2004'
)
or
(
(
ResourceCode like 'CHEM%'
)
AND ApptDate >= '8/13/2004'
)
or
(
(
ResourceCode like 'PREOP%'
)
AND ApptDate >= '2/14/2005'
)
or
(
(
ResourceCode like 'UWHC%'
or ResourceCode like 'REN%'
or ResourceCode like 'SUR%'
)
AND ApptDate >= '3/08/2005'
)
or
(
(
ResourceCode like 'SON%'
or ResourceCode like 'BON%'
or ResourceCode like 'BMO%'
)
AND ApptDate >= '03/14/2005'
)
or
(
(
ResourceCode like 'JEI%'
or ResourceCode like 'NEU%'
or ResourceCode like 'ACRC%'
or ResourceCode like 'DON%'
or ResourceCode like 'HON%'
or ResourceCode like 'JON%'
or ResourceCode like 'UON%'
or ResourceCode like 'ORT%'
)
AND ApptDate >= '04/07/2005'
)
or
(
(
ResourceCode like 'OON%'
)
AND ApptDate >= '5/23/2005'
)
or
(
(
ResourceCode like 'NSG%'
or ResourceCode like 'JTS%'
)
AND ApptDate >= '7/27/2005'
)
or
(
(
ResourceCode like 'URO%'
)
AND ApptDate >= '7/28/2005'
)
or
(
(
ResourceCode like 'LT%'
)
AND ApptDate >= '11/14/2005'
)
or
(
(
ResourceCode = 'CVTOBLE'
or ResourceCode = 'CVANTAK'
)
AND ApptDate >= '10/12/2005'
)
or
(
(
ResourceCode like 'INFCH%'
or ResourceCode like 'INFRM%'
or ResourceCode like 'MICHAIR%'
or ResourceCode like 'MIEXP%'
or ResourceCode like 'MIROOM%'
)
AND ApptDate >= '11/07/2005'
)
)
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
"CD" <mcdye1@.hotmail.REMOVETHIS.com> wrote in message
news:eYmG3WCIGHA.1132@.TK2MSFTNGP10.phx.gbl...
> I am not a programmer but have noticed in this query peaking my processor
> when running. The table has over 2.7 million rows. Is there a better way
> to write the sp for better performance or tweak the table? The excution
> plan shows TableScan 37%>Filter 56%> Sort 4%>pParallelism/Gather Stream
3%>
> Select 0%.
> -- TABLE:
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Appointment]') and OBJECTPROPERTY(id, N'IsUserTable') =
> 1)
> drop table [dbo].[Appointment]
> GO
> CREATE TABLE [dbo].[Appointment] (
> [RecordID] [int] IDENTITY (1, 1) NOT NULL ,
> [CPINumber] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [VisitSuffix] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
> [PatientName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
> ,
> [DOB] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Sex] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Race] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SSN] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ApptLocation] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
> ,
> [ResourceCode] [varchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
> ,
> [ResourceGroup] [varchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [ApptDate] [datetime] NOT NULL ,
> [Duration] [int] NULL ,
> [EncounterID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CreatedBy] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CreatedDate] [datetime] NULL ,
> [LastChangeBy] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [LastChangeDate] [datetime] NULL ,
> [ApptStatus] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
> [InterfaceStatus] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
> ,
> [ActivityType] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Comments] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Comments2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Arrived] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [COLD] [datetime] NULL ,
> [Comments3] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Comments4] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PatientTypeCode] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [FinancialClassCode] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Scraped] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [BaseDate] [datetime] NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Appointment] WITH NOCHECK ADD
> CONSTRAINT [DF_Appointment_Scraped] DEFAULT (0) FOR [Scraped],
> CONSTRAINT [PK_Appointment] PRIMARY KEY NONCLUSTERED
> (
> [RecordID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [IX_ResourceCode] ON [dbo].[Appointment]([ResourceCode])
WITH
> FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [IX_AppointmentCPIDate] ON [dbo].[Appointment]([CPINumber],
> [ApptDate]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [IX_Appointment] ON [dbo].[Appointment]([CPINumber],
> [VisitSuffix], [ActivityType], [ApptStatus]) WITH FILLFACTOR = 90 ON
> [PRIMARY]
> GO
> CREATE INDEX [IX_Appointment_ResourceGroup] ON
> [dbo].[Appointment]([ResourceGroup]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
>
> -- STORED PROCEDURE::
> CREATE Procedure spGetApptsForInterface
> As
> SELECT *
> FROM dbo.Appointment
> WHERE ((ResourceCode LIKE 'SHC%' OR
> ResourceCode LIKE 'GM%' OR
> ResourceCode LIKE 'GI%' or ResourceCode like 'PUL%'
or
> ResourceCode like 'CAR%' or ResourceCode like 'MED%') AND (ApptDate >=
> '04/07/2003') AND (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate,
> GETDATE()) > 4)) OR
> (((ResourceCode = 'GHCCARAD') or (resourcecode =
> 'GHCJONES')) AND (ApptDate >= '01/27/2003') AND (InterfaceStatus = 'R')
AND
> (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) OR
> (((ResourceCode = 'OPCNUTRI') or (resourcecode like
> 'MON%') or (resourcecode like 'GON%') or (resourcecode like 'LON%') or
> (resourcecode like 'END%')) AND (ApptDate >= '07/01/2003') AND
> (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) OR
> (((ResourceCode LIKE 'ID%') AND (ApptDate >= '08/12/2003') AND
> (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) or
> (((ResourceCode = 'SUREIDTS') or (ResourceCode = 'SUREIDTW') or
> (ResourceCode = 'VASLAB1') or (ResourceCode = 'VASLAB2')) AND (ApptDate >=
> '12/01/2003') AND (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate,
> GETDATE()) > 4)) or
> (((ResourceCode = 'PFT') or (ResourceCode = 'PFT2')) AND
(ApptDate
CreatedDate,
> GETDATE()) > 4)) or
> (((ResourceCode like 'CWC%') or (ResourceCode like 'TEL%')) AND
> (ApptDate >= '4/13/2004') AND (InterfaceStatus = 'R') AND (DATEDIFF(mi,
> CreatedDate, GETDATE()) > 4)) or
> (ResourceCode like 'CHEM%' AND ApptDate >= '8/13/2004' AND
> InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
> (ResourceCode like 'PREOP%' AND ApptDate >= '2/14/2005' AND
> InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
> (ResourceCode like 'UWHC%' AND ApptDate >=
'3/08/2005'
> AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
> (ResourceCode like 'REN%' AND ApptDate >=
'3/08/2005'
> AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
> (ResourceCode like 'SUR%' AND ApptDate >=
'3/08/2005'
> AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
> (((ResourceCode like 'SON%') or (ResourceCode like
> 'BON%') or (ResourceCode like 'BMO%')) AND (ApptDate >= '03/14/2005') AND
> (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) or
> (((ResourceCode like 'JEI%') or (ResourceCode like
> 'NEU%') or (ResourceCode like 'ACRC%')) AND (ApptDate >= '04/07/2005') AND
> (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) or
> (((ResourceCode like 'DON%') or (ResourceCode like
> 'HON%') or (ResourceCode like 'JON%') or (ResourceCode like 'UON%') or
> (ResourceCode like 'ORT%')) AND (ApptDate >= '04/07/2005') AND
> (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) or
> (ResourceCode like 'OON%' AND ApptDate >=
'5/23/2005'
> AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
> (ResourceCode like 'NSG%' AND ApptDate >=
'7/27/2005'
> AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
> (ResourceCode like 'JTS%' AND ApptDate >=
'7/27/2005'
> AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
> (ResourceCode like 'URO%' AND ApptDate >=
'7/28/2005'
> AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
> (ResourceCode like 'LT%' AND ApptDate >=
'11/14/2005'
> AND InterfaceStatus = 'R' AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4) or
> (((ResourceCode = 'CVTOBLE') or (ResourceCode =
> 'CVANTAK')) AND (ApptDate >= '10/12/2005') AND (InterfaceStatus = 'R') AND
> (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) or
> (((ResourceCode like 'INFCH%') or (ResourceCode like
> 'INFRM%')) AND (ApptDate >= '11/07/2005') AND (InterfaceStatus = 'R') AND
> (DATEDIFF(mi, CreatedDate, GETDATE()) > 4)) or
> (((ResourceCode like 'MICHAIR%') or (ResourceCode
like
> 'MIEXP%') or (ResourceCode like 'MIROOM%')) AND (ApptDate >= '11/07/2005')
> AND (InterfaceStatus = 'R') AND (DATEDIFF(mi, CreatedDate, GETDATE()) >
4)))
>
> ORDER BY ApptDate
> return
> GO
>|||I should note that I consolidate some of the resource code comparisons where
the dates were the same.
Also, you might try changing all those OR clauses to unions, although I am
not certain if SQL Server will handle them the same or not. The idea is
that the unions will be ~ 12 independent queries which may individually
process quite fast, as compared to the ORs which MAY require the database to
process every single row in order to evaluate all conditions.
Not fully understanding the SQL server tuning engine, I cant be sure if
either of these ideas will make any difference at all. Adding the indexes
as suggested by others may make a much bigger difference wihtout changing
the code.
Just in case, here is the SQL as a bunch of unions...
SELECT *
FROM dbo.Appointment
WHERE
(
ResourceCode LIKE 'SHC%'
or ResourceCode LIKE 'GM%'
or ResourceCode LIKE 'GI%'
or ResourceCode like 'PUL%'
or ResourceCode like 'CAR%'
or ResourceCode like 'MED%'
)
AND ApptDate >= '04/07/2003'
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
Union all
SELECT *
FROM dbo.Appointment
WHERE
(
ResourceCode = 'GHCCARAD'
or resourcecode = 'GHCJONES'
)
AND ApptDate >= '01/27/2003'
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
Union all
SELECT *
FROM dbo.Appointment
WHERE
(
ResourceCode = 'OPCNUTRI'
or resourcecode like 'MON%'
or resourcecode like 'GON%'
or resourcecode like 'LON%'
or resourcecode like 'END%'
)
AND ApptDate >= '07/01/2003'
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
Union all
SELECT *
FROM dbo.Appointment
WHERE
(
ResourceCode LIKE 'ID%'
)
AND ApptDate >= '08/12/2003'
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
Union all
SELECT *
FROM dbo.Appointment
WHERE
(
ResourceCode = 'SUREIDTS'
or ResourceCode = 'SUREIDTW'
or ResourceCode = 'VASLAB1'
or ResourceCode = 'VASLAB2'
)
AND ApptDate >= '12/01/2003'
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
Union all
SELECT *
FROM dbo.Appointment
WHERE
(
ResourceCode = 'PFT'
or ResourceCode = 'PFT2'
)
AND ApptDate >= '1/13/2004'
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
Union all
SELECT *
FROM dbo.Appointment
WHERE
(
ResourceCode like 'CWC%'
or ResourceCode like 'TEL%'
)
AND ApptDate >= '4/13/2004'
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
Union all
SELECT *
FROM dbo.Appointment
WHERE
(
ResourceCode like 'CHEM%'
)
AND ApptDate >= '8/13/2004'
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
Union all
SELECT *
FROM dbo.Appointment
WHERE
(
ResourceCode like 'PREOP%'
)
AND ApptDate >= '2/14/2005'
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
Union all
SELECT *
FROM dbo.Appointment
WHERE
(
ResourceCode like 'UWHC%'
or ResourceCode like 'REN%'
or ResourceCode like 'SUR%'
)
AND ApptDate >= '3/08/2005'
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
Union all
SELECT *
FROM dbo.Appointment
WHERE
(
ResourceCode like 'SON%'
or ResourceCode like 'BON%'
or ResourceCode like 'BMO%'
)
AND ApptDate >= '03/14/2005'
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
Union all
SELECT *
FROM dbo.Appointment
WHERE
(
ResourceCode like 'JEI%'
or ResourceCode like 'NEU%'
or ResourceCode like 'ACRC%'
or ResourceCode like 'DON%'
or ResourceCode like 'HON%'
or ResourceCode like 'JON%'
or ResourceCode like 'UON%'
or ResourceCode like 'ORT%'
)
AND ApptDate >= '04/07/2005'
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
Union all
SELECT *
FROM dbo.Appointment
WHERE
(
ResourceCode like 'OON%'
)
AND ApptDate >= '5/23/2005'
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
Union all
SELECT *
FROM dbo.Appointment
WHERE
(
ResourceCode like 'NSG%'
or ResourceCode like 'JTS%'
)
AND ApptDate >= '7/27/2005'
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
Union all
SELECT *
FROM dbo.Appointment
WHERE
(
ResourceCode like 'URO%'
)
AND ApptDate >= '7/28/2005'
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
Union all
SELECT *
FROM dbo.Appointment
WHERE
(
ResourceCode like 'LT%'
)
AND ApptDate >= '11/14/2005'
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
Union all
SELECT *
FROM dbo.Appointment
WHERE
(
ResourceCode = 'CVTOBLE'
or ResourceCode = 'CVANTAK'
)
AND ApptDate >= '10/12/2005'
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
Union all
SELECT *
FROM dbo.Appointment
WHERE
(
ResourceCode like 'INFCH%'
or ResourceCode like 'INFRM%'
or ResourceCode like 'MICHAIR%'
or ResourceCode like 'MIEXP%'
or ResourceCode like 'MIROOM%'
)
AND ApptDate >= '11/07/2005'
AND InterfaceStatus = 'R'
AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:%23y7yjkDIGHA.2912@.tk2msftngp13.phx.gbl...
> First, the following logic is repeated in every part of this query (every
or
> clause) and should appear only once.
> AND InterfaceStatus = 'R'
> AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
> Moving this out of the nested ors MAY allow the database to filter on
these
> fields up front, thus reducing your dataset before processing all the ORs.
> Second, reformatting this miserable code to group things more logically,
and
> removing many of the redundant () will make ti much mroe readable and thus
> easier to understand. I left some redundant () in place for consistency
> reasons, but the following should be much easier to follow.
> SELECT *
> FROM dbo.Appointment
> WHERE
> (
> (
> (
> ResourceCode LIKE 'SHC%'
> or ResourceCode LIKE 'GM%'
> or ResourceCode LIKE 'GI%'
> or ResourceCode like 'PUL%'
> or ResourceCode like 'CAR%'
> or ResourceCode like 'MED%'
> )
> AND ApptDate >= '04/07/2003'
> )
> or
> (
> (
> ResourceCode = 'GHCCARAD'
> or resourcecode = 'GHCJONES'
> )
> AND ApptDate >= '01/27/2003'
> )
> or
> (
> (
> ResourceCode = 'OPCNUTRI'
> or resourcecode like 'MON%'
> or resourcecode like 'GON%'
> or resourcecode like 'LON%'
> or resourcecode like 'END%'
> )
> AND ApptDate >= '07/01/2003'
> )
> or
> (
> (
> ResourceCode LIKE 'ID%'
> )
> AND ApptDate >= '08/12/2003'
> )
> or
> (
> (
> ResourceCode = 'SUREIDTS'
> or ResourceCode = 'SUREIDTW'
> or ResourceCode = 'VASLAB1'
> or ResourceCode = 'VASLAB2'
> )
> AND ApptDate >= '12/01/2003'
> )
> or
> (
> (
> ResourceCode = 'PFT'
> or ResourceCode = 'PFT2'
> )
> AND ApptDate >= '1/13/2004'
> )
> or
> (
> (
> ResourceCode like 'CWC%'
> or ResourceCode like 'TEL%'
> )
> AND ApptDate >= '4/13/2004'
> )
> or
> (
> (
> ResourceCode like 'CHEM%'
> )
> AND ApptDate >= '8/13/2004'
> )
> or
> (
> (
> ResourceCode like 'PREOP%'
> )
> AND ApptDate >= '2/14/2005'
> )
> or
> (
> (
> ResourceCode like 'UWHC%'
> or ResourceCode like 'REN%'
> or ResourceCode like 'SUR%'
> )
> AND ApptDate >= '3/08/2005'
> )
> or
> (
> (
> ResourceCode like 'SON%'
> or ResourceCode like 'BON%'
> or ResourceCode like 'BMO%'
> )
> AND ApptDate >= '03/14/2005'
> )
> or
> (
> (
> ResourceCode like 'JEI%'
> or ResourceCode like 'NEU%'
> or ResourceCode like 'ACRC%'
> or ResourceCode like 'DON%'
> or ResourceCode like 'HON%'
> or ResourceCode like 'JON%'
> or ResourceCode like 'UON%'
> or ResourceCode like 'ORT%'
> )
> AND ApptDate >= '04/07/2005'
> )
> or
> (
> (
> ResourceCode like 'OON%'
> )
> AND ApptDate >= '5/23/2005'
> )
> or
> (
> (
> ResourceCode like 'NSG%'
> or ResourceCode like 'JTS%'
> )
> AND ApptDate >= '7/27/2005'
> )
> or
> (
> (
> ResourceCode like 'URO%'
> )
> AND ApptDate >= '7/28/2005'
> )
> or
> (
> (
> ResourceCode like 'LT%'
> )
> AND ApptDate >= '11/14/2005'
> )
> or
> (
> (
> ResourceCode = 'CVTOBLE'
> or ResourceCode = 'CVANTAK'
> )
> AND ApptDate >= '10/12/2005'
> )
> or
> (
> (
> ResourceCode like 'INFCH%'
> or ResourceCode like 'INFRM%'
> or ResourceCode like 'MICHAIR%'
> or ResourceCode like 'MIEXP%'
> or ResourceCode like 'MIROOM%'
> )
> AND ApptDate >= '11/07/2005'
> )
> )
> AND InterfaceStatus = 'R'
> AND DATEDIFF(mi, CreatedDate, GETDATE()) > 4
>
> "CD" <mcdye1@.hotmail.REMOVETHIS.com> wrote in message
> news:eYmG3WCIGHA.1132@.TK2MSFTNGP10.phx.gbl...
processor
way
> 3%>
=
,
NULL
> ,
> NULL
> NULL
> NULL
NULL
> ,
,
,
NULL
> ,
> NULL
,
NULL
> ,
> WITH
[dbo]. [Appointment]([CPINumber],[color=darkred
]
'PUL%'
> or
> AND
like
OR
or
>=
> (ApptDate
> CreatedDate,
> '3/08/2005'
or
> '3/08/2005'
or
> '3/08/2005'
or
AND
or
AND
or
or
> '5/23/2005'
or
> '7/27/2005'
or
> '7/27/2005'
or
> '7/28/2005'
or
> '11/14/2005'
or
AND
like
AND
> like
'11/07/2005')
> 4)))
>sql
Query/Table Join
Hey guys,
I need to perform a query on two tables that look like this:
ATTENDANCE
attendanceid
memberid
meetingid
meetingdate
MEMBERS
memberid
firstname
lastaname
I can run this query just fine:
select m.memberid from members m, attendance a where
a.meetingid = 47 and m.memberid = a.memberid
This gives me memberid's for members that are present.
I need members that are not present.
select m.memberid from members m, attendance a where
a.meetingid = 47 and m.memberid <> a.memberid
This returns a thousand rows when it should return no more than 25.
I am sure this is just a simple join but I do not have my SQL for dummies book with me at the moment ;-)
Thanks,
MikeI guess this is what u r trying for.
select memberid from members where memberid not in(select memberid from attendance where meetingid = 47)
Originally posted by mycoolross
Hey guys,
I need to perform a query on two tables that look like this:
ATTENDANCE
attendanceid
memberid
meetingid
meetingdate
MEMBERS
memberid
firstname
lastaname
I can run this query just fine:
select m.memberid from members m, attendance a where
a.meetingid = 47 and m.memberid = a.memberid
This gives me memberid's for members that are present.
I need members that are not present.
select m.memberid from members m, attendance a where
a.meetingid = 47 and m.memberid <> a.memberid
This returns a thousand rows when it should return no more than 25.
I am sure this is just a simple join but I do not have my SQL for dummies book with me at the moment ;-)
Thanks,
Mike|||THANK YOU!!
I need to perform a query on two tables that look like this:
ATTENDANCE
attendanceid
memberid
meetingid
meetingdate
MEMBERS
memberid
firstname
lastaname
I can run this query just fine:
select m.memberid from members m, attendance a where
a.meetingid = 47 and m.memberid = a.memberid
This gives me memberid's for members that are present.
I need members that are not present.
select m.memberid from members m, attendance a where
a.meetingid = 47 and m.memberid <> a.memberid
This returns a thousand rows when it should return no more than 25.
I am sure this is just a simple join but I do not have my SQL for dummies book with me at the moment ;-)
Thanks,
MikeI guess this is what u r trying for.
select memberid from members where memberid not in(select memberid from attendance where meetingid = 47)
Originally posted by mycoolross
Hey guys,
I need to perform a query on two tables that look like this:
ATTENDANCE
attendanceid
memberid
meetingid
meetingdate
MEMBERS
memberid
firstname
lastaname
I can run this query just fine:
select m.memberid from members m, attendance a where
a.meetingid = 47 and m.memberid = a.memberid
This gives me memberid's for members that are present.
I need members that are not present.
select m.memberid from members m, attendance a where
a.meetingid = 47 and m.memberid <> a.memberid
This returns a thousand rows when it should return no more than 25.
I am sure this is just a simple join but I do not have my SQL for dummies book with me at the moment ;-)
Thanks,
Mike|||THANK YOU!!
Query/SQL help
I have two tables
Table1
Item# -Descr-Amt
xyz1- Orange - 250.00
xyz2 - Apple - 350.00
Table 2
Item#-Tax-Amt
xyz1-Tax1- 2.50
xyz1-Tax2-3.50
xyz2-Tax1-3.00
xyz2-Tax2-4.20
I will like have a query to show data as
Item#-Descr-Amt-Tax1-Tax2
xyz1-Orange-250.00-2.50-3.50
xyz2-Apple-350.00-3.00-4.20
Thanks for any help.http://www.aspfaq.com/show.asp?id=2462
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Joe O" <jp3BlessNoSpam@.hotmail.com> wrote in message
news:OpFicqj2FHA.3272@.TK2MSFTNGP09.phx.gbl...
> I have two tables
> Table1
> Item# -Descr-Amt
> xyz1- Orange - 250.00
> xyz2 - Apple - 350.00
> Table 2
> Item#-Tax-Amt
> xyz1-Tax1- 2.50
> xyz1-Tax2-3.50
> xyz2-Tax1-3.00
> xyz2-Tax2-4.20
> I will like have a query to show data as
> Item#-Descr-Amt-Tax1-Tax2
> xyz1-Orange-250.00-2.50-3.50
> xyz2-Apple-350.00-3.00-4.20
> Thanks for any help.
>
>|||Try this query
select a.Item# , Descr , a.Amt , b.Amt as Tax1 , ( select e.Amt
from
table1 d ,
table2 e
where
d.Item# = e.Item#
and Tax = 'Tax2' and d.Item#
= a.Item#
and a.Descr = d.Descr
) as Tax2
from table1 a ,
table2 b
where a.Item# = b.Item# and Tax = 'Tax1'
"Joe O" wrote:
> I have two tables
> Table1
> Item# -Descr-Amt
> xyz1- Orange - 250.00
> xyz2 - Apple - 350.00
> Table 2
> Item#-Tax-Amt
> xyz1-Tax1- 2.50
> xyz1-Tax2-3.50
> xyz2-Tax1-3.00
> xyz2-Tax2-4.20
> I will like have a query to show data as
> Item#-Descr-Amt-Tax1-Tax2
> xyz1-Orange-250.00-2.50-3.50
> xyz2-Apple-350.00-3.00-4.20
> Thanks for any help.
>
>|||Thanks Adam,Tarik... I am checking out the tips
"Tarik" <Tarik@.discussions.microsoft.com> wrote in message
news:2333E900-BF9F-4C43-BE24-02AD2DAB4650@.microsoft.com...
> Try this query
> select a.Item# , Descr , a.Amt , b.Amt as Tax1 , ( select e.Amt
> from
> table1 d ,
> table2 e
> where
> d.Item# = e.Item#
> and Tax = 'Tax2' and d.Item#
> = a.Item#
> and a.Descr = d.Descr
> ) as Tax2
> from table1 a ,
> table2 b
> where a.Item# = b.Item# and Tax = 'Tax1'
>
>
> "Joe O" wrote:
>
Table1
Item# -Descr-Amt
xyz1- Orange - 250.00
xyz2 - Apple - 350.00
Table 2
Item#-Tax-Amt
xyz1-Tax1- 2.50
xyz1-Tax2-3.50
xyz2-Tax1-3.00
xyz2-Tax2-4.20
I will like have a query to show data as
Item#-Descr-Amt-Tax1-Tax2
xyz1-Orange-250.00-2.50-3.50
xyz2-Apple-350.00-3.00-4.20
Thanks for any help.http://www.aspfaq.com/show.asp?id=2462
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Joe O" <jp3BlessNoSpam@.hotmail.com> wrote in message
news:OpFicqj2FHA.3272@.TK2MSFTNGP09.phx.gbl...
> I have two tables
> Table1
> Item# -Descr-Amt
> xyz1- Orange - 250.00
> xyz2 - Apple - 350.00
> Table 2
> Item#-Tax-Amt
> xyz1-Tax1- 2.50
> xyz1-Tax2-3.50
> xyz2-Tax1-3.00
> xyz2-Tax2-4.20
> I will like have a query to show data as
> Item#-Descr-Amt-Tax1-Tax2
> xyz1-Orange-250.00-2.50-3.50
> xyz2-Apple-350.00-3.00-4.20
> Thanks for any help.
>
>|||Try this query
select a.Item# , Descr , a.Amt , b.Amt as Tax1 , ( select e.Amt
from
table1 d ,
table2 e
where
d.Item# = e.Item#
and Tax = 'Tax2' and d.Item#
= a.Item#
and a.Descr = d.Descr
) as Tax2
from table1 a ,
table2 b
where a.Item# = b.Item# and Tax = 'Tax1'
"Joe O" wrote:
> I have two tables
> Table1
> Item# -Descr-Amt
> xyz1- Orange - 250.00
> xyz2 - Apple - 350.00
> Table 2
> Item#-Tax-Amt
> xyz1-Tax1- 2.50
> xyz1-Tax2-3.50
> xyz2-Tax1-3.00
> xyz2-Tax2-4.20
> I will like have a query to show data as
> Item#-Descr-Amt-Tax1-Tax2
> xyz1-Orange-250.00-2.50-3.50
> xyz2-Apple-350.00-3.00-4.20
> Thanks for any help.
>
>|||Thanks Adam,Tarik... I am checking out the tips
"Tarik" <Tarik@.discussions.microsoft.com> wrote in message
news:2333E900-BF9F-4C43-BE24-02AD2DAB4650@.microsoft.com...
> Try this query
> select a.Item# , Descr , a.Amt , b.Amt as Tax1 , ( select e.Amt
> from
> table1 d ,
> table2 e
> where
> d.Item# = e.Item#
> and Tax = 'Tax2' and d.Item#
> = a.Item#
> and a.Descr = d.Descr
> ) as Tax2
> from table1 a ,
> table2 b
> where a.Item# = b.Item# and Tax = 'Tax1'
>
>
> "Joe O" wrote:
>
Subscribe to:
Posts (Atom)