I need to run a sql script to update records in a table that were created
that day. I have a huge database and it would take a while to run a script
on all.
Here's what i have so far: (this is a select stmt version not update stmt
i'm using for testing)
SELECT tm5user.matter.c_date as CreateDate,
((tm5user.matter.c_date)-(1800-12-28))as CalcDate, *
FROM tm5user.matter
INNER JOIN tm5user.billopt ON tm5user.matter.sysid = tm5user.billopt.owner_id
WHERE tm5user.matter.c_date>=((tm5user.matter.c_date)-(1800-12-28))
I got the script to work but it doesn't calculate the date correctly. The
date field "c_date" is based on a clarion base date of 12/28/1800.
I have my select statement return the "c_date" field, the calculated date
field, and full table columns for testing. The CreateDate and CalcDate
should be equal and they are not.
Does anyone have any ideas ... I tried CastDate that also did not work.
Any help or tips would be great ..... thanks.
rob bartley
msce> ((tm5user.matter.c_date)-(1800-12-28))as CalcDate
The above expression (1800-12-28) is doing integer arithmetic. I suggest you use the DATEDIFF or
DATEADD function (depending on what you want to achieve). Also, I suggest you format the date in
language neutral format ('yyyymmdd') so it doesn't break in a nationalized environment.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Rob" <temp@.dstek.com> wrote in message news:us1cOsGtDHA.1884@.TK2MSFTNGP10.phx.gbl...
> I need to run a sql script to update records in a table that were created
> that day. I have a huge database and it would take a while to run a script
> on all.
> Here's what i have so far: (this is a select stmt version not update stmt
> i'm using for testing)
> SELECT tm5user.matter.c_date as CreateDate,
> ((tm5user.matter.c_date)-(1800-12-28))as CalcDate, *
> FROM tm5user.matter
> INNER JOIN tm5user.billopt ON tm5user.matter.sysid => tm5user.billopt.owner_id
> WHERE tm5user.matter.c_date>=((tm5user.matter.c_date)-(1800-12-28))
>
> I got the script to work but it doesn't calculate the date correctly. The
> date field "c_date" is based on a clarion base date of 12/28/1800.
> I have my select statement return the "c_date" field, the calculated date
> field, and full table columns for testing. The CreateDate and CalcDate
> should be equal and they are not.
> Does anyone have any ideas ... I tried CastDate that also did not work.
> Any help or tips would be great ..... thanks.
> rob bartley
> msce
>
Showing posts with label records. Show all posts
Showing posts with label records. Show all posts
Wednesday, March 28, 2012
Wednesday, March 21, 2012
Problem with simple Where Clause
Please Help me. I have a Stored Proc as follows:
USE feesched
GO
CREATE PROCEDURE [dbo].[sp_UpdateAveragedMedicare]
AS
-- copy records with alpha in pos 1 that's not J
SELECT SUBSTRING([cpt code],2,LEN([cpt code])),amount,inscode
INTO t_AveragedMedicare
FROM AveragedMedicare
WHERE NOT ISNUMERIC(LEFT([cpt code],1))
AND LEFT([cpt code],1) <> 'J'
I'm getting the following error message:
Server: Msg 156, Level 15, State 1, Procedure sp_UpdateAveragedMedicare, Line 10
Incorrect syntax near the keyword 'AND'.
Can anyone help me out?
Thanks!
Tony"Tony" <topoulos@.mchsi.com> wrote in message
news:6b5416f0.0403041238.31850940@.posting.google.c om...
> Please Help me. I have a Stored Proc as follows:
> USE feesched
> GO
> CREATE PROCEDURE [dbo].[sp_UpdateAveragedMedicare]
> AS
> -- copy records with alpha in pos 1 that's not J
> SELECT SUBSTRING([cpt code],2,LEN([cpt code])),amount,inscode
> INTO t_AveragedMedicare
> FROM AveragedMedicare
> WHERE NOT ISNUMERIC(LEFT([cpt code],1))
> AND LEFT([cpt code],1) <> 'J'
> I'm getting the following error message:
> Server: Msg 156, Level 15, State 1, Procedure sp_UpdateAveragedMedicare,
Line 10
> Incorrect syntax near the keyword 'AND'.
> Can anyone help me out?
> Thanks!
> Tony
ISNUMERIC() returns an integer, so I guess you want this:
WHERE ISNUMERIC(LEFT([cpt code],1)) = 0
However, be aware that ISNUMERIC() returns 1 for many things which you may
not consider to be a number, as it will evaluate conversion to any numeric
data type, not just integers. Perhaps this would be better:
WHERE [cpt code] NOT LIKE '[0-9]%'
AND [cpt code] NOT LIKE 'J%'
Also, note that using procedure names beginning with sp_ is usually reserved
for system stored procedures, and isn't recommended for application code.
Simon
Tuesday, March 20, 2012
Problem with Select Top ....(incorrect syntax near '@p')
hello,
i want to return a nr of records depending on the parameter @.nrRecords
I tryed to execute this in the queryanalyser - but i get a error, what is
wrong?
Declare @.nrRecords int
SET @.nrRecords=10
SELECT TOP @.p companyName
FROM myTable
the error is
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '@.nrRecords'.
thanksexamnotes (td1369@.discussions.microsoft.com) writes:
> i want to return a nr of records depending on the parameter @.nrRecords
> I tryed to execute this in the queryanalyser - but i get a error, what is
> wrong?
> Declare @.nrRecords int
> SET @.nrRecords=10
> SELECT TOP @.p companyName
> FROM myTable
> the error is
> Server: Msg 170, Level 15, State 1, Line 4
> Line 4: Incorrect syntax near '@.nrRecords'.
In SQL 2000 you must specify a constant with TOP. In SQL 2005 you can
use an expression, but it must be in parens:
SELECT TOP(@.nrRecords)
The best alternative in SQL 2000, is to use SET ROWCOUNT instead:
SET ROWCOUNT @.nrRecords
SELECT companyName
FROM myTable
SET ROWCOUNT 0
By the way, TOP or SET ROWCOUNT without an ORDER BY is not really
meaningful, is it is not defined which rows you get.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Da ich sch=E4tze, da=DF Du mit einem SQL Server 2k unterwegs bist, mu=DF
ich Dir leider sagen, da=DF dies die variable TOP Deklaration erst ab
SQL2k5 funtkioniert, anonsten bleibt Dir nur noch Dynamic SQL =FCbrig.
http://www.sommarskog.se/dynamic_sql.html
HTH, jens Suessmeyer.|||Sorry posted in the wrong language:
I assume that your base is a SQL 2k, unfortunately this doesn=B4t work
with this, this declaration only works with SQL2k, therefore onyl
dynamic SQL remains as a solution for you.
http://www.sommarskog.se/dynamic_sql.html=20
HTH, jens Suessmeyer.|||> i want to return a nr of records depending on the parameter @.nrRecords
> I tryed to execute this in the queryanalyser - but i get a error, what is
> wrong?
> Declare @.nrRecords int
> SET @.nrRecords=10
> SELECT TOP @.p companyName
> FROM myTable
Two problems.
(1) in SQL Server 2000, TOP does not take a parameter.
(2) what on earth does your TOP mean? You don't have an ORDER BY! TOP is
meaningless without ORDER BY. SQL Server is free to return the rows in any
order it deems fit, which means your result can change completely from one
execution to the next.
I'll assume you meant to put an ORDER BY clause, and your solution might
look like this:
DECLARE @.nrRecords INT
SET @.nrRecords = 10
SET ROWCOUNT @.nrRecords
SELECT companyName
FROM myTable
ORDER BY companyName
SET ROWCOUNT 0|||good idea with the ROWCOUNT ...,
thanks - it works
best resgards
"Aaron Bertrand [SQL Server MVP]" wrote:
> Two problems.
> (1) in SQL Server 2000, TOP does not take a parameter.
> (2) what on earth does your TOP mean? You don't have an ORDER BY! TOP is
> meaningless without ORDER BY. SQL Server is free to return the rows in an
y
> order it deems fit, which means your result can change completely from one
> execution to the next.
> I'll assume you meant to put an ORDER BY clause, and your solution might
> look like this:
> DECLARE @.nrRecords INT
> SET @.nrRecords = 10
> SET ROWCOUNT @.nrRecords
> SELECT companyName
> FROM myTable
> ORDER BY companyName
> SET ROWCOUNT 0
>
>
i want to return a nr of records depending on the parameter @.nrRecords
I tryed to execute this in the queryanalyser - but i get a error, what is
wrong?
Declare @.nrRecords int
SET @.nrRecords=10
SELECT TOP @.p companyName
FROM myTable
the error is
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '@.nrRecords'.
thanksexamnotes (td1369@.discussions.microsoft.com) writes:
> i want to return a nr of records depending on the parameter @.nrRecords
> I tryed to execute this in the queryanalyser - but i get a error, what is
> wrong?
> Declare @.nrRecords int
> SET @.nrRecords=10
> SELECT TOP @.p companyName
> FROM myTable
> the error is
> Server: Msg 170, Level 15, State 1, Line 4
> Line 4: Incorrect syntax near '@.nrRecords'.
In SQL 2000 you must specify a constant with TOP. In SQL 2005 you can
use an expression, but it must be in parens:
SELECT TOP(@.nrRecords)
The best alternative in SQL 2000, is to use SET ROWCOUNT instead:
SET ROWCOUNT @.nrRecords
SELECT companyName
FROM myTable
SET ROWCOUNT 0
By the way, TOP or SET ROWCOUNT without an ORDER BY is not really
meaningful, is it is not defined which rows you get.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Da ich sch=E4tze, da=DF Du mit einem SQL Server 2k unterwegs bist, mu=DF
ich Dir leider sagen, da=DF dies die variable TOP Deklaration erst ab
SQL2k5 funtkioniert, anonsten bleibt Dir nur noch Dynamic SQL =FCbrig.
http://www.sommarskog.se/dynamic_sql.html
HTH, jens Suessmeyer.|||Sorry posted in the wrong language:
I assume that your base is a SQL 2k, unfortunately this doesn=B4t work
with this, this declaration only works with SQL2k, therefore onyl
dynamic SQL remains as a solution for you.
http://www.sommarskog.se/dynamic_sql.html=20
HTH, jens Suessmeyer.|||> i want to return a nr of records depending on the parameter @.nrRecords
> I tryed to execute this in the queryanalyser - but i get a error, what is
> wrong?
> Declare @.nrRecords int
> SET @.nrRecords=10
> SELECT TOP @.p companyName
> FROM myTable
Two problems.
(1) in SQL Server 2000, TOP does not take a parameter.
(2) what on earth does your TOP mean? You don't have an ORDER BY! TOP is
meaningless without ORDER BY. SQL Server is free to return the rows in any
order it deems fit, which means your result can change completely from one
execution to the next.
I'll assume you meant to put an ORDER BY clause, and your solution might
look like this:
DECLARE @.nrRecords INT
SET @.nrRecords = 10
SET ROWCOUNT @.nrRecords
SELECT companyName
FROM myTable
ORDER BY companyName
SET ROWCOUNT 0|||good idea with the ROWCOUNT ...,
thanks - it works
best resgards
"Aaron Bertrand [SQL Server MVP]" wrote:
> Two problems.
> (1) in SQL Server 2000, TOP does not take a parameter.
> (2) what on earth does your TOP mean? You don't have an ORDER BY! TOP is
> meaningless without ORDER BY. SQL Server is free to return the rows in an
y
> order it deems fit, which means your result can change completely from one
> execution to the next.
> I'll assume you meant to put an ORDER BY clause, and your solution might
> look like this:
> DECLARE @.nrRecords INT
> SET @.nrRecords = 10
> SET ROWCOUNT @.nrRecords
> SELECT companyName
> FROM myTable
> ORDER BY companyName
> SET ROWCOUNT 0
>
>
Problem with select every TOP 1 records from different group in a
Hi Expert,
I came across a situation where the query result returns all non-NULL
records even I use TOP 1 statement. Here is the SQL statement:
SELECT CONTRACT_NUMBER, STEP, STATUS_END_DATE, CIS_PK
FROM dbo.[VIEW1] T1
WHERE (CONTRACT_NUMBER = 'S07-123A' OR
CONTRACT_NUMBER = 'S07-127A' OR
CONTRACT_NUMBER = 'S07-129A') AND (STEP =
(SELECT TOP 1 STEP
FROM dbo.[VIEW2] T2
WHERE T2.CIS_PK = T1.CIS_PK AND
T2.STATUS_END_DATE IS NOT NULL
ORDER BY STEP ASC))
For the above SQL statement, "CIS_PK is key". "STEP" is unique within each
CONTRACT_NUMBER group.
The incorrect result that I got is:
CONTRACT_NUMBER STEP DATE
CIS_PK
S05-137A 4 8/11/2006
728
S05-137A 7 10/1/2006
731
I am expecting the right answer, which is:
S05-137A 4 8/11/2006
728
Thanks,
-adam
I just figured it out by myself. The where clause comparison
T2.CIS_PK=T1.CIS_PK should be T2.CONTRACT_NUMBER = T1.CONTRACT_NUMBER.
Thanks anyway.
-adam
"adam" wrote:
> Hi Expert,
> I came across a situation where the query result returns all non-NULL
> records even I use TOP 1 statement. Here is the SQL statement:
> SELECT CONTRACT_NUMBER, STEP, STATUS_END_DATE, CIS_PK
> FROM dbo.[VIEW1] T1
> WHERE (CONTRACT_NUMBER = 'S07-123A' OR
> CONTRACT_NUMBER = 'S07-127A' OR
> CONTRACT_NUMBER = 'S07-129A') AND (STEP =
> (SELECT TOP 1 STEP
> FROM dbo.[VIEW2] T2
> WHERE T2.CIS_PK = T1.CIS_PK AND
> T2.STATUS_END_DATE IS NOT NULL
> ORDER BY STEP ASC))
> For the above SQL statement, "CIS_PK is key". "STEP" is unique within each
> CONTRACT_NUMBER group.
> The incorrect result that I got is:
> CONTRACT_NUMBER STEP DATE
> CIS_PK
> S05-137A 4 8/11/2006
> 728
> S05-137A 7 10/1/2006
> 731
> I am expecting the right answer, which is:
> S05-137A 4 8/11/2006
> 728
> Thanks,
> -adam
I came across a situation where the query result returns all non-NULL
records even I use TOP 1 statement. Here is the SQL statement:
SELECT CONTRACT_NUMBER, STEP, STATUS_END_DATE, CIS_PK
FROM dbo.[VIEW1] T1
WHERE (CONTRACT_NUMBER = 'S07-123A' OR
CONTRACT_NUMBER = 'S07-127A' OR
CONTRACT_NUMBER = 'S07-129A') AND (STEP =
(SELECT TOP 1 STEP
FROM dbo.[VIEW2] T2
WHERE T2.CIS_PK = T1.CIS_PK AND
T2.STATUS_END_DATE IS NOT NULL
ORDER BY STEP ASC))
For the above SQL statement, "CIS_PK is key". "STEP" is unique within each
CONTRACT_NUMBER group.
The incorrect result that I got is:
CONTRACT_NUMBER STEP DATE
CIS_PK
S05-137A 4 8/11/2006
728
S05-137A 7 10/1/2006
731
I am expecting the right answer, which is:
S05-137A 4 8/11/2006
728
Thanks,
-adam
I just figured it out by myself. The where clause comparison
T2.CIS_PK=T1.CIS_PK should be T2.CONTRACT_NUMBER = T1.CONTRACT_NUMBER.
Thanks anyway.
-adam
"adam" wrote:
> Hi Expert,
> I came across a situation where the query result returns all non-NULL
> records even I use TOP 1 statement. Here is the SQL statement:
> SELECT CONTRACT_NUMBER, STEP, STATUS_END_DATE, CIS_PK
> FROM dbo.[VIEW1] T1
> WHERE (CONTRACT_NUMBER = 'S07-123A' OR
> CONTRACT_NUMBER = 'S07-127A' OR
> CONTRACT_NUMBER = 'S07-129A') AND (STEP =
> (SELECT TOP 1 STEP
> FROM dbo.[VIEW2] T2
> WHERE T2.CIS_PK = T1.CIS_PK AND
> T2.STATUS_END_DATE IS NOT NULL
> ORDER BY STEP ASC))
> For the above SQL statement, "CIS_PK is key". "STEP" is unique within each
> CONTRACT_NUMBER group.
> The incorrect result that I got is:
> CONTRACT_NUMBER STEP DATE
> CIS_PK
> S05-137A 4 8/11/2006
> 728
> S05-137A 7 10/1/2006
> 731
> I am expecting the right answer, which is:
> S05-137A 4 8/11/2006
> 728
> Thanks,
> -adam
Wednesday, March 7, 2012
problem with repeating records
hello i'm using asp.net 2 with VB and i have a table in my db the problem is that table has a repeated records likethis image
now i want to update the absence record but cos it's repeated it will only update one record what i want is to apply the update on all other records ? hope u can help me .thankscan you rephrase your question? What is the UPDATE statement you have currently?|||hello ndinakar, i will explain my prob again i have a page has a details view and sqldatasource the details show one record as u know there are some records repeated as in the image i posted i'm using the sqldatasource auto statement update, but it doesnt update and it did updated once but only one value and the other repeatd values still as they are what i want is when i update one value it auto update all other repeated values, hope this clear and hope u can help me.|||What is the UPDATE statement you have? Do you have any primary key for the table? Pls post the table structure and some sample data.|||ok this my table's columns :
ID uniqueidentifier(primary key)
serial Int
stage(1st grade,2nd grade,...etc) char
class nchar
subject varchar
marks char
total nchar
absence nchar
ranking char
now i want to update the absence record but cos it's repeated it will only update one record what i want is to apply the update on all other records ? hope u can help me .thankscan you rephrase your question? What is the UPDATE statement you have currently?|||hello ndinakar, i will explain my prob again i have a page has a details view and sqldatasource the details show one record as u know there are some records repeated as in the image i posted i'm using the sqldatasource auto statement update, but it doesnt update and it did updated once but only one value and the other repeatd values still as they are what i want is when i update one value it auto update all other repeated values, hope this clear and hope u can help me.|||What is the UPDATE statement you have? Do you have any primary key for the table? Pls post the table structure and some sample data.|||ok this my table's columns :
ID uniqueidentifier(primary key)
serial Int
stage(1st grade,2nd grade,...etc) char
class nchar
subject varchar
marks char
total nchar
absence nchar
ranking char
the update statement is the auto generated statement in the sqldatasource here's the sqldatasource code in my page:
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues"
ConnectionString="<%$ ConnectionStrings:aspnetcon %>" DeleteCommand="DELETE FROM [grades] WHERE [id] = @.original_id AND [serial] = @.original_serial AND [stage] = @.original_stage AND [class] = @.original_class AND [subject] = @.original_subject AND [marks] = @.original_marks AND [total1] = @.original_total1 AND [total2] = @.original_total2 AND [status] = @.original_status AND [absence] = @.original_absence AND [ranking] = @.original_ranking"
InsertCommand="INSERT INTO [grades] ([id], [serial], [stage], [class], [subject], [marks], [total1], [total2], [status], [absence], [ranking]) VALUES (@.id, @.serial, @.stage, @.class, @.subject, @.marks, @.total1, @.total2, @.status, @.absence, @.ranking)"
OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [grades] WHERE (([stage] = @.stage) AND ([subject] = @.subject) AND ([class] = @.class))"
UpdateCommand="UPDATE [grades] SET [serial] = @.serial, [stage] = @.stage, [class] = @.class, [subject] = @.subject, [marks] = @.marks, [total1] = @.total1, [total2] = @.total2, [status] = @.status, [absence] = @.absence, [ranking] = @.ranking WHERE [id] = @.original_id AND [serial] = @.original_serial AND [stage] = @.original_stage AND [class] = @.original_class AND [subject] = @.original_subject AND [marks] = @.original_marks AND [total1] = @.original_total1 AND [total2] = @.original_total2 AND [status] = @.original_status AND [absence] = @.original_absence AND [ranking] = @.original_ranking">
<DeleteParameters>
<asp:Parameter Name="original_id" Type="Object" />
<asp:Parameter Name="original_serial" Type="Int32" />
<asp:Parameter Name="original_stage" Type="String" />
<asp:Parameter Name="original_class" Type="String" />
<asp:Parameter Name="original_subject" Type="String" />
<asp:Parameter Name="original_marks" Type="String" />
<asp:Parameter Name="original_total1" Type="String" />
<asp:Parameter Name="original_total2" Type="String" />
<asp:Parameter Name="original_status" Type="String" />
<asp:Parameter Name="original_absence" Type="String" />
<asp:Parameter Name="original_ranking" Type="String" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="serial" Type="Int32" />
<asp:Parameter Name="stage" Type="String" />
<asp:Parameter Name="class" Type="String" />
<asp:Parameter Name="subject" Type="String" />
<asp:Parameter Name="marks" Type="String" />
<asp:Parameter Name="total1" Type="String" />
<asp:Parameter Name="total2" Type="String" />
<asp:Parameter Name="status" Type="String" />
<asp:Parameter Name="absence" Type="String" />
<asp:Parameter Name="ranking" Type="String" />
<asp:Parameter Name="original_id" Type="Object" />
<asp:Parameter Name="original_serial" Type="Int32" />
<asp:Parameter Name="original_stage" Type="String" />
<asp:Parameter Name="original_class" Type="String" />
<asp:Parameter Name="original_subject" Type="String" />
<asp:Parameter Name="original_marks" Type="String" />
<asp:Parameter Name="original_total1" Type="String" />
<asp:Parameter Name="original_total2" Type="String" />
<asp:Parameter Name="original_status" Type="String" />
<asp:Parameter Name="original_absence" Type="String" />
<asp:Parameter Name="original_ranking" Type="String" />
</UpdateParameters>
<SelectParameters>
<asp:CookieParameter CookieName="stg" Name="stage" Type="String" />
<asp:CookieParameter CookieName="sub" Name="subject" Type="String" />
<asp:ControlParameter ControlID="DropDownList1" Name="class" PropertyName="SelectedValue"
Type="String" />
</SelectParameters>
<InsertParameters>
<asp:Parameter Name="id" Type="Object" />
<asp:Parameter Name="serial" Type="Int32" />
<asp:Parameter Name="stage" Type="String" />
<asp:Parameter Name="class" Type="String" />
<asp:Parameter Name="subject" Type="String" />
<asp:Parameter Name="marks" Type="String" />
<asp:Parameter Name="total1" Type="String" />
<asp:Parameter Name="total2" Type="String" />
<asp:Parameter Name="status" Type="String" />
<asp:Parameter Name="absence" Type="String" />
<asp:Parameter Name="ranking" Type="String" />
</InsertParameters>
</asp:SqlDataSource
the problem is there's a 5 or 6 subjects to every student and each subject in the table has a repeated record here's aone studentin my table so as u can see in this image the repeating records for example the absence and status columns, what i want is really simple i want when i update the absence for example i want to apply that update on all records not only one record hope u understand me .thanks for the help|||sorry. I am having internet problems at home so I could not reply earlier. I think if you remove the @.original_Id parameter from your where clause you could update all the records. Basically your WHERE condition is limiting the records. Based on your screenshot it appears all the other columns have same values except the @.original_id which is not in the screenshot. So I am guessing that is the condition limiting your results.
Problem with query taking a long time.
I have a large SQL table, called Event, that has 4.6 million records in it.
My problem is that one particular query on the table is taking over 30
seconds to run. All the other queries that I have take under 1 second to
run. I have tried numerous index changes, but it seems that nothing helps.
I have even removed all the indexes (excluding the primary key and clustered
index), and had the Index Tuning Wizard generate new indexes, but with no
luck. Below I have listed the SQL Queries that I am running, and the SQL
statements for creating all of the related tables and views. You will notic
e
that I am calling a view to retrieve the records. Any suggestions would be
greatly appreciated.
Good Query 1 (< 1 sec to run on 4.6 million records)
SELECT top 15 * FROM vw_HistEventDisplayGrid
WHERE SeqNo NOT IN (SELECT TOP 0 SeqNo FROM vw_HistEventDisplayGrid
WHERE (NAME like 'a%') AND (Sphere = 0 OR Sphere IN (SELECT Partition FROM
Sphere WHERE OperPrivId = 33))
ORDER BY NAME ASC, EDATE DESC)
AND (NAME like 'a%') AND (Sphere = 0 OR Sphere IN (SELECT Partition FROM
Sphere WHERE OperPrivId = 33))
ORDER BY NAME ASC, EDATE DESC
Good Query 2 (< 1 sec to run on 4.6 million records)
SELECT top 15 * FROM vw_HistEventDisplayGrid
WHERE SeqNo NOT IN (SELECT TOP 0 SeqNo FROM vw_HistEventDisplayGrid
WHERE (Sphere = 0 OR Sphere IN (SELECT Partition FROM Sphere WHERE
OperPrivId = 33))
ORDER BY NAME DESC, EDATE DESC)
AND (Sphere = 0 OR Sphere IN (SELECT Partition FROM Sphere WHERE OperPrivId
= 33))
ORDER BY NAME DESC, EDATE DESC
Bad Query (> 30 seconds on 4.6 million records)
SELECT top 15 * FROM vw_HistEventDisplayGrid
WHERE SeqNo NOT IN (SELECT TOP 0 SeqNo FROM vw_HistEventDisplayGrid
WHERE (Sphere = 0 OR Sphere IN (SELECT Partition FROM Sphere WHERE
OperPrivId = 33))
ORDER BY NAME ASC, EDATE DESC)
AND (Sphere = 0 OR Sphere IN (SELECT Partition FROM Sphere WHERE OperPrivId
= 33))
ORDER BY NAME ASC, EDATE DESC
SQL for creating tables and views:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[vw_HistEventDisplayGrid]') and OBJECTPROPERTY(id,
N'IsView') = 1)
drop view [dbo].[vw_HistEventDisplayGrid]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Event]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Event]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Oper]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Oper]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Sphere]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Sphere]
GO
CREATE TABLE [dbo].[Event] (
[Seqno] uniqueidentifier ROWGUIDCOL NOT NULL ,
[Priority] [smallint] NULL ,
[Cat] [smallint] NULL ,
[PnlNo] [smallint] NULL ,
[EDate] [datetime] NULL ,
[Number] [smallint] NULL ,
[Status] [smallint] NULL ,
[Facno] [smallint] NULL ,
[Badge] [bigint] NULL ,
[Class] [varchar] (48) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Name] [varchar] (48) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Arch] [smallint] NULL ,
[AckOpr] [smallint] NULL ,
[AckTStamp] [datetime] NULL ,
[Actions] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RespReq] [bit] NULL ,
[Sphere] [int] NULL ,
[Tag] [int] NULL ,
[Pending] [bit] NULL ,
[HasPhoto] [bit] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[Oper] (
[OpNo] [smallint] NOT NULL ,
[ScrName] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Name] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Passwrd] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Priv] [int] NULL ,
[Changed] [datetime] NULL ,
[GroupNo] [smallint] NULL ,
[AckTO] [smallint] NULL ,
[Opt] [smallint] NULL ,
[Flag] [bit] NULL ,
[AutoAck] [smallint] NULL ,
[ShowPrior] [smallint] NULL ,
[UP_CBCaptions] [bit] NULL ,
[UP_CBIcons] [bit] NULL ,
[UP_UseBg] [bit] NULL ,
[UP_BgIndex] [smallint] NULL ,
[UP_X] [smallint] NULL ,
[UP_Y] [smallint] NULL ,
[UP_Width] [smallint] NULL ,
[UP_Height] [smallint] NULL ,
[UP_Monitor] [bit] NULL ,
[UP_Control] [bit] NULL ,
[UP_ShowPhoto] [bit] NULL ,
[LogOffTime] [int] NULL ,
[EventViewCount] [int] NULL ,
[EventViewDay] [bit] NULL ,
[EventViewSpan] [int] NULL ,
[WebRights] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OperFontName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OperFontSize] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Sphere] (
[OperPrivId] [smallint] NOT NULL ,
[GroupId] [smallint] NOT NULL ,
[GroupNo] [smallint] NOT NULL ,
[Changed] [datetime] NULL ,
[Partition] [int] NOT NULL ,
[Restriction] [smallint] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Oper] WITH NOCHECK ADD
CONSTRAINT [PK_Oper] PRIMARY KEY CLUSTERED
(
[OpNo]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Sphere] WITH NOCHECK ADD
CONSTRAINT [PK_Sphere] PRIMARY KEY CLUSTERED
(
[OperPrivId],
[GroupId],
[GroupNo]
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [EdateX_Event] ON [dbo].[Event]([EDate]) ON
[PRIMARY]
GO
ALTER TABLE [dbo].[Event] ADD
CONSTRAINT [DF_Event_Seqno] DEFAULT (newid()) FOR [Seqno],
CONSTRAINT [DF__Event__HasPhoto__3DE82FB7] DEFAULT (0) FOR [HasPhoto],
CONSTRAINT [PK_Event] PRIMARY KEY NONCLUSTERED
(
[Seqno]
) ON [PRIMARY]
GO
CREATE INDEX [Event50] ON [dbo].[Event]([AckOpr], [EDate] DESC , [Seqno],
[Class], [Description]) ON [PRIMARY]
GO
CREATE INDEX [Event51] ON [dbo].[Event]([Description], [EDate] DESC ,
[Seqno], [Class]) ON [PRIMARY]
GO
CREATE INDEX [Event52] ON [dbo].[Event]([Class], [EDate] DESC , [Seqno],
[Description]) ON [PRIMARY]
GO
CREATE INDEX [Event53] ON [dbo].[Event]([Class] DESC , [EDate] DESC ,
[Seqno], [Description]) ON [PRIMARY]
GO
CREATE INDEX [Event54] ON [dbo].[Event]([Description]) ON [PRIMARY]
GO
CREATE INDEX [Event55] ON [dbo].[Event]([AckOpr]) ON [PRIMARY]
GO
CREATE INDEX [Event56] ON [dbo].[Event]([Sphere]) ON [PRIMARY]
GO
CREATE INDEX [Event59] ON [dbo].[Event]([Sphere], [Name]) ON [PRIMARY]
GO
CREATE INDEX [Event57] ON [dbo].[Event]([Name], [EDate] DESC ) ON [PRIMARY]
GO
CREATE INDEX [Event58] ON [dbo].[Event]([Name] DESC , [EDate] DESC ) ON
[PRIMARY]
GO
ALTER TABLE [dbo].[Oper] ADD
CONSTRAINT [DF_Oper_OpNo] DEFAULT (1) FOR [OpNo],
CONSTRAINT [DF_Oper_Priv] DEFAULT (0) FOR [Priv],
CONSTRAINT [DF_Oper_GroupNo] DEFAULT (0) FOR [GroupNo],
CONSTRAINT [DF_Oper_AckTO] DEFAULT (60) FOR [AckTO],
CONSTRAINT [DF_Oper_AutoAck] DEFAULT (0) FOR [AutoAck],
CONSTRAINT [DF_Oper_UP_ShowPhoto] DEFAULT (0) FOR [UP_ShowPhoto],
CONSTRAINT [DF_Oper_LogOffTime] DEFAULT (5) FOR [LogOffTime],
CONSTRAINT [DF_Oper_EventViewCount] DEFAULT (50) FOR [EventViewCount]
GO
CREATE INDEX [PKO_Group] ON [dbo].[Oper]([GroupNo]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Sphere] ADD
CONSTRAINT [DF_Sphere_OperPrivId] DEFAULT (1) FOR [OperPrivId],
CONSTRAINT [DF_Sphere_GroupId] DEFAULT (0) FOR [GroupId],
CONSTRAINT [DF_Sphere_GroupNo] DEFAULT (1) FOR [GroupNo],
CONSTRAINT [DF_Sphere_Partition] DEFAULT (0) FOR [Partition]
GO
CREATE INDEX [PKS_GroupID] ON [dbo].[Sphere]([GroupId]) ON [PRIMARY]
GO
CREATE INDEX [PKS_Group] ON [dbo].[Sphere]([GroupNo]) ON [PRIMARY]
GO
CREATE INDEX [PKS_OperPrivID] ON [dbo].[Sphere]([OperPrivId]) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.vw_HistEventDisplayGrid
AS
SELECT dbo.Event.Seqno, dbo.Event.Priority, dbo.Event.Cat,
dbo.Event.PnlNo, dbo.Event.EDate, dbo.Event.Number, dbo.Event.Status,
dbo.Event.Facno,
dbo.Event.Badge, dbo.Event.Class,
dbo.Event.Description, dbo.Event.Name, dbo.Event.Arch, dbo.Event.AckOpr,
dbo.Event.AckTStamp,
dbo.Event.Actions, dbo.Event.RespReq,
dbo.Event.Sphere, dbo.Event.Tag, dbo.Event.Pending, dbo.Event.HasPhoto,
dbo.Oper.ScrName
FROM dbo.Event LEFT OUTER JOIN
dbo.Oper ON dbo.Event.AckOpr = dbo.Oper.OpNo
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO"Reza Raquib" <RezaRaquib@.discussions.microsoft.com> wrote in message
news:3B46DDF5-8361-4C96-9925-70ACB477B03F@.microsoft.com...
> Good Query 2 (< 1 sec to run on 4.6 million records)
> SELECT top 15 * FROM vw_HistEventDisplayGrid
> WHERE SeqNo NOT IN (SELECT TOP 0 SeqNo FROM vw_HistEventDisplayGrid
> WHERE (Sphere = 0 OR Sphere IN (SELECT Partition FROM Sphere WHERE
> OperPrivId = 33))
> ORDER BY NAME DESC, EDATE DESC)
> AND (Sphere = 0 OR Sphere IN (SELECT Partition FROM Sphere WHERE
OperPrivId
> = 33))
> ORDER BY NAME DESC, EDATE DESC
> Bad Query (> 30 seconds on 4.6 million records)
> SELECT top 15 * FROM vw_HistEventDisplayGrid
> WHERE SeqNo NOT IN (SELECT TOP 0 SeqNo FROM vw_HistEventDisplayGrid
> WHERE (Sphere = 0 OR Sphere IN (SELECT Partition FROM Sphere WHERE
> OperPrivId = 33))
> ORDER BY NAME ASC, EDATE DESC)
> AND (Sphere = 0 OR Sphere IN (SELECT Partition FROM Sphere WHERE
OperPrivId
> = 33))
> ORDER BY NAME ASC, EDATE DESC
>
I'm not sure if I'm missing something, but I see no differences between
these two queries -- did you paste the wrong one?
Also, did you really mean SELECT TOP 0 ?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||The top 0 is dynamic, as a form of paging. Replace the top 0 with a top 90,
that would be more representative. The only difference between these 2
queries are the ordering of the NAME column. The good query has the NAME
ordered descending. The bad query has the NAME ascending.
Thanks.
"Adam Machanic" wrote:
> "Reza Raquib" <RezaRaquib@.discussions.microsoft.com> wrote in message
> news:3B46DDF5-8361-4C96-9925-70ACB477B03F@.microsoft.com...
> OperPrivId
> OperPrivId
>
> I'm not sure if I'm missing something, but I see no differences between
> these two queries -- did you paste the wrong one?
> Also, did you really mean SELECT TOP 0 ?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
>|||"Reza Raquib" <RezaRaquib@.discussions.microsoft.com> wrote in message
news:4E9E19FD-624F-4D2F-AE2B-171783E7266D@.microsoft.com...
> The top 0 is dynamic, as a form of paging. Replace the top 0 with a top
90,
> that would be more representative. The only difference between these 2
> queries are the ordering of the NAME column. The good query has the NAME
> ordered descending. The bad query has the NAME ascending.
I think you'll find that re-doing the paging a bit differently is going to
be the best solution -- however, paging is never an easy problem in SQL
Server...
Give this a try and tell me if it helps at all -- or if it compeltely bombs
:) -- (replace both <N>s with the last row number you want, and <M> with the
first row number you want):
SELECT *
FROM (
SELECT TOP <n>
E1.Seqno,
E1.Priority,
E1.Cat,
E1.PnlNo,
E1.EDate,
E1.Number,
E1.Status,
E1.Facno,
E1.Badge,
E1.Class,
E1.Description,
E1.Name,
E1.Arch,
E1.AckOpr,
E1.AckTStamp,
E1.Actions,
E1.RespReq,
E1.Sphere,
E1.Tag,
E1.Pending,
E1.HasPhoto,
E1.ScrName,
COUNT(*) AS Rank
FROM vw_HistEventDisplayGrid E1
JOIN vw_HistEventDisplayGrid E2 ON E2.Name <= E1.Name
AND (E2.Name < E1.Name OR (E2.EDate > E1.EDate))
WHERE EXISTS
(SELECT 1
WHERE E1.Sphere = 0
UNION ALL
SELECT 1
FROM Sphere
WHERE OperPrivId = 33
AND Sphere.Partition = E1.Sphere)
AND EXISTS
(SELECT 1
WHERE E2.Sphere = 0
UNION ALL
SELECT 1
FROM Sphere
WHERE OperPrivId = 33
AND Sphere.Partition = E2.Sphere)
GROUP BY
E1.Seqno,
E1.Priority,
E1.Cat,
E1.PnlNo,
E1.EDate,
E1.Number,
E1.Status,
E1.Facno,
E1.Badge,
E1.Class,
E1.Description,
E1.Name,
E1.Arch,
E1.AckOpr,
E1.AckTStamp,
E1.Actions,
E1.RespReq,
E1.Sphere,
E1.Tag,
E1.Pending,
E1.HasPhoto,
E1.ScrName
ORDER BY E1.NAME ASC, E1.EDATE DESC) y
WHERE Y.theCount BETWEEN <m> AND <n>
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
My problem is that one particular query on the table is taking over 30
seconds to run. All the other queries that I have take under 1 second to
run. I have tried numerous index changes, but it seems that nothing helps.
I have even removed all the indexes (excluding the primary key and clustered
index), and had the Index Tuning Wizard generate new indexes, but with no
luck. Below I have listed the SQL Queries that I am running, and the SQL
statements for creating all of the related tables and views. You will notic
e
that I am calling a view to retrieve the records. Any suggestions would be
greatly appreciated.
Good Query 1 (< 1 sec to run on 4.6 million records)
SELECT top 15 * FROM vw_HistEventDisplayGrid
WHERE SeqNo NOT IN (SELECT TOP 0 SeqNo FROM vw_HistEventDisplayGrid
WHERE (NAME like 'a%') AND (Sphere = 0 OR Sphere IN (SELECT Partition FROM
Sphere WHERE OperPrivId = 33))
ORDER BY NAME ASC, EDATE DESC)
AND (NAME like 'a%') AND (Sphere = 0 OR Sphere IN (SELECT Partition FROM
Sphere WHERE OperPrivId = 33))
ORDER BY NAME ASC, EDATE DESC
Good Query 2 (< 1 sec to run on 4.6 million records)
SELECT top 15 * FROM vw_HistEventDisplayGrid
WHERE SeqNo NOT IN (SELECT TOP 0 SeqNo FROM vw_HistEventDisplayGrid
WHERE (Sphere = 0 OR Sphere IN (SELECT Partition FROM Sphere WHERE
OperPrivId = 33))
ORDER BY NAME DESC, EDATE DESC)
AND (Sphere = 0 OR Sphere IN (SELECT Partition FROM Sphere WHERE OperPrivId
= 33))
ORDER BY NAME DESC, EDATE DESC
Bad Query (> 30 seconds on 4.6 million records)
SELECT top 15 * FROM vw_HistEventDisplayGrid
WHERE SeqNo NOT IN (SELECT TOP 0 SeqNo FROM vw_HistEventDisplayGrid
WHERE (Sphere = 0 OR Sphere IN (SELECT Partition FROM Sphere WHERE
OperPrivId = 33))
ORDER BY NAME ASC, EDATE DESC)
AND (Sphere = 0 OR Sphere IN (SELECT Partition FROM Sphere WHERE OperPrivId
= 33))
ORDER BY NAME ASC, EDATE DESC
SQL for creating tables and views:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[vw_HistEventDisplayGrid]') and OBJECTPROPERTY(id,
N'IsView') = 1)
drop view [dbo].[vw_HistEventDisplayGrid]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Event]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Event]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Oper]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Oper]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Sphere]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Sphere]
GO
CREATE TABLE [dbo].[Event] (
[Seqno] uniqueidentifier ROWGUIDCOL NOT NULL ,
[Priority] [smallint] NULL ,
[Cat] [smallint] NULL ,
[PnlNo] [smallint] NULL ,
[EDate] [datetime] NULL ,
[Number] [smallint] NULL ,
[Status] [smallint] NULL ,
[Facno] [smallint] NULL ,
[Badge] [bigint] NULL ,
[Class] [varchar] (48) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Name] [varchar] (48) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Arch] [smallint] NULL ,
[AckOpr] [smallint] NULL ,
[AckTStamp] [datetime] NULL ,
[Actions] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RespReq] [bit] NULL ,
[Sphere] [int] NULL ,
[Tag] [int] NULL ,
[Pending] [bit] NULL ,
[HasPhoto] [bit] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[Oper] (
[OpNo] [smallint] NOT NULL ,
[ScrName] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Name] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Passwrd] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Priv] [int] NULL ,
[Changed] [datetime] NULL ,
[GroupNo] [smallint] NULL ,
[AckTO] [smallint] NULL ,
[Opt] [smallint] NULL ,
[Flag] [bit] NULL ,
[AutoAck] [smallint] NULL ,
[ShowPrior] [smallint] NULL ,
[UP_CBCaptions] [bit] NULL ,
[UP_CBIcons] [bit] NULL ,
[UP_UseBg] [bit] NULL ,
[UP_BgIndex] [smallint] NULL ,
[UP_X] [smallint] NULL ,
[UP_Y] [smallint] NULL ,
[UP_Width] [smallint] NULL ,
[UP_Height] [smallint] NULL ,
[UP_Monitor] [bit] NULL ,
[UP_Control] [bit] NULL ,
[UP_ShowPhoto] [bit] NULL ,
[LogOffTime] [int] NULL ,
[EventViewCount] [int] NULL ,
[EventViewDay] [bit] NULL ,
[EventViewSpan] [int] NULL ,
[WebRights] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OperFontName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OperFontSize] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Sphere] (
[OperPrivId] [smallint] NOT NULL ,
[GroupId] [smallint] NOT NULL ,
[GroupNo] [smallint] NOT NULL ,
[Changed] [datetime] NULL ,
[Partition] [int] NOT NULL ,
[Restriction] [smallint] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Oper] WITH NOCHECK ADD
CONSTRAINT [PK_Oper] PRIMARY KEY CLUSTERED
(
[OpNo]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Sphere] WITH NOCHECK ADD
CONSTRAINT [PK_Sphere] PRIMARY KEY CLUSTERED
(
[OperPrivId],
[GroupId],
[GroupNo]
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [EdateX_Event] ON [dbo].[Event]([EDate]) ON
[PRIMARY]
GO
ALTER TABLE [dbo].[Event] ADD
CONSTRAINT [DF_Event_Seqno] DEFAULT (newid()) FOR [Seqno],
CONSTRAINT [DF__Event__HasPhoto__3DE82FB7] DEFAULT (0) FOR [HasPhoto],
CONSTRAINT [PK_Event] PRIMARY KEY NONCLUSTERED
(
[Seqno]
) ON [PRIMARY]
GO
CREATE INDEX [Event50] ON [dbo].[Event]([AckOpr], [EDate] DESC , [Seqno],
[Class], [Description]) ON [PRIMARY]
GO
CREATE INDEX [Event51] ON [dbo].[Event]([Description], [EDate] DESC ,
[Seqno], [Class]) ON [PRIMARY]
GO
CREATE INDEX [Event52] ON [dbo].[Event]([Class], [EDate] DESC , [Seqno],
[Description]) ON [PRIMARY]
GO
CREATE INDEX [Event53] ON [dbo].[Event]([Class] DESC , [EDate] DESC ,
[Seqno], [Description]) ON [PRIMARY]
GO
CREATE INDEX [Event54] ON [dbo].[Event]([Description]) ON [PRIMARY]
GO
CREATE INDEX [Event55] ON [dbo].[Event]([AckOpr]) ON [PRIMARY]
GO
CREATE INDEX [Event56] ON [dbo].[Event]([Sphere]) ON [PRIMARY]
GO
CREATE INDEX [Event59] ON [dbo].[Event]([Sphere], [Name]) ON [PRIMARY]
GO
CREATE INDEX [Event57] ON [dbo].[Event]([Name], [EDate] DESC ) ON [PRIMARY]
GO
CREATE INDEX [Event58] ON [dbo].[Event]([Name] DESC , [EDate] DESC ) ON
[PRIMARY]
GO
ALTER TABLE [dbo].[Oper] ADD
CONSTRAINT [DF_Oper_OpNo] DEFAULT (1) FOR [OpNo],
CONSTRAINT [DF_Oper_Priv] DEFAULT (0) FOR [Priv],
CONSTRAINT [DF_Oper_GroupNo] DEFAULT (0) FOR [GroupNo],
CONSTRAINT [DF_Oper_AckTO] DEFAULT (60) FOR [AckTO],
CONSTRAINT [DF_Oper_AutoAck] DEFAULT (0) FOR [AutoAck],
CONSTRAINT [DF_Oper_UP_ShowPhoto] DEFAULT (0) FOR [UP_ShowPhoto],
CONSTRAINT [DF_Oper_LogOffTime] DEFAULT (5) FOR [LogOffTime],
CONSTRAINT [DF_Oper_EventViewCount] DEFAULT (50) FOR [EventViewCount]
GO
CREATE INDEX [PKO_Group] ON [dbo].[Oper]([GroupNo]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Sphere] ADD
CONSTRAINT [DF_Sphere_OperPrivId] DEFAULT (1) FOR [OperPrivId],
CONSTRAINT [DF_Sphere_GroupId] DEFAULT (0) FOR [GroupId],
CONSTRAINT [DF_Sphere_GroupNo] DEFAULT (1) FOR [GroupNo],
CONSTRAINT [DF_Sphere_Partition] DEFAULT (0) FOR [Partition]
GO
CREATE INDEX [PKS_GroupID] ON [dbo].[Sphere]([GroupId]) ON [PRIMARY]
GO
CREATE INDEX [PKS_Group] ON [dbo].[Sphere]([GroupNo]) ON [PRIMARY]
GO
CREATE INDEX [PKS_OperPrivID] ON [dbo].[Sphere]([OperPrivId]) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.vw_HistEventDisplayGrid
AS
SELECT dbo.Event.Seqno, dbo.Event.Priority, dbo.Event.Cat,
dbo.Event.PnlNo, dbo.Event.EDate, dbo.Event.Number, dbo.Event.Status,
dbo.Event.Facno,
dbo.Event.Badge, dbo.Event.Class,
dbo.Event.Description, dbo.Event.Name, dbo.Event.Arch, dbo.Event.AckOpr,
dbo.Event.AckTStamp,
dbo.Event.Actions, dbo.Event.RespReq,
dbo.Event.Sphere, dbo.Event.Tag, dbo.Event.Pending, dbo.Event.HasPhoto,
dbo.Oper.ScrName
FROM dbo.Event LEFT OUTER JOIN
dbo.Oper ON dbo.Event.AckOpr = dbo.Oper.OpNo
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO"Reza Raquib" <RezaRaquib@.discussions.microsoft.com> wrote in message
news:3B46DDF5-8361-4C96-9925-70ACB477B03F@.microsoft.com...
> Good Query 2 (< 1 sec to run on 4.6 million records)
> SELECT top 15 * FROM vw_HistEventDisplayGrid
> WHERE SeqNo NOT IN (SELECT TOP 0 SeqNo FROM vw_HistEventDisplayGrid
> WHERE (Sphere = 0 OR Sphere IN (SELECT Partition FROM Sphere WHERE
> OperPrivId = 33))
> ORDER BY NAME DESC, EDATE DESC)
> AND (Sphere = 0 OR Sphere IN (SELECT Partition FROM Sphere WHERE
OperPrivId
> = 33))
> ORDER BY NAME DESC, EDATE DESC
> Bad Query (> 30 seconds on 4.6 million records)
> SELECT top 15 * FROM vw_HistEventDisplayGrid
> WHERE SeqNo NOT IN (SELECT TOP 0 SeqNo FROM vw_HistEventDisplayGrid
> WHERE (Sphere = 0 OR Sphere IN (SELECT Partition FROM Sphere WHERE
> OperPrivId = 33))
> ORDER BY NAME ASC, EDATE DESC)
> AND (Sphere = 0 OR Sphere IN (SELECT Partition FROM Sphere WHERE
OperPrivId
> = 33))
> ORDER BY NAME ASC, EDATE DESC
>
I'm not sure if I'm missing something, but I see no differences between
these two queries -- did you paste the wrong one?
Also, did you really mean SELECT TOP 0 ?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||The top 0 is dynamic, as a form of paging. Replace the top 0 with a top 90,
that would be more representative. The only difference between these 2
queries are the ordering of the NAME column. The good query has the NAME
ordered descending. The bad query has the NAME ascending.
Thanks.
"Adam Machanic" wrote:
> "Reza Raquib" <RezaRaquib@.discussions.microsoft.com> wrote in message
> news:3B46DDF5-8361-4C96-9925-70ACB477B03F@.microsoft.com...
> OperPrivId
> OperPrivId
>
> I'm not sure if I'm missing something, but I see no differences between
> these two queries -- did you paste the wrong one?
> Also, did you really mean SELECT TOP 0 ?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
>|||"Reza Raquib" <RezaRaquib@.discussions.microsoft.com> wrote in message
news:4E9E19FD-624F-4D2F-AE2B-171783E7266D@.microsoft.com...
> The top 0 is dynamic, as a form of paging. Replace the top 0 with a top
90,
> that would be more representative. The only difference between these 2
> queries are the ordering of the NAME column. The good query has the NAME
> ordered descending. The bad query has the NAME ascending.
I think you'll find that re-doing the paging a bit differently is going to
be the best solution -- however, paging is never an easy problem in SQL
Server...
Give this a try and tell me if it helps at all -- or if it compeltely bombs
:) -- (replace both <N>s with the last row number you want, and <M> with the
first row number you want):
SELECT *
FROM (
SELECT TOP <n>
E1.Seqno,
E1.Priority,
E1.Cat,
E1.PnlNo,
E1.EDate,
E1.Number,
E1.Status,
E1.Facno,
E1.Badge,
E1.Class,
E1.Description,
E1.Name,
E1.Arch,
E1.AckOpr,
E1.AckTStamp,
E1.Actions,
E1.RespReq,
E1.Sphere,
E1.Tag,
E1.Pending,
E1.HasPhoto,
E1.ScrName,
COUNT(*) AS Rank
FROM vw_HistEventDisplayGrid E1
JOIN vw_HistEventDisplayGrid E2 ON E2.Name <= E1.Name
AND (E2.Name < E1.Name OR (E2.EDate > E1.EDate))
WHERE EXISTS
(SELECT 1
WHERE E1.Sphere = 0
UNION ALL
SELECT 1
FROM Sphere
WHERE OperPrivId = 33
AND Sphere.Partition = E1.Sphere)
AND EXISTS
(SELECT 1
WHERE E2.Sphere = 0
UNION ALL
SELECT 1
FROM Sphere
WHERE OperPrivId = 33
AND Sphere.Partition = E2.Sphere)
GROUP BY
E1.Seqno,
E1.Priority,
E1.Cat,
E1.PnlNo,
E1.EDate,
E1.Number,
E1.Status,
E1.Facno,
E1.Badge,
E1.Class,
E1.Description,
E1.Name,
E1.Arch,
E1.AckOpr,
E1.AckTStamp,
E1.Actions,
E1.RespReq,
E1.Sphere,
E1.Tag,
E1.Pending,
E1.HasPhoto,
E1.ScrName
ORDER BY E1.NAME ASC, E1.EDATE DESC) y
WHERE Y.theCount BETWEEN <m> AND <n>
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
Saturday, February 25, 2012
Problem with query
Hi,
I have a query that is supposed to return records and make a left join where
one field is not null, but for some reason is not working properly and
returns the records even though they are null.
SELECT *
FROM cases a
left join activities as w on a.id = w.caseid AND w.Dateinitiated = (Select
MAX(y.Dateinitiated)
From Activities y Where y.caseid = a.id AND y.ActType ='HISTORY' and
y.dateinitiated IS NOT NULL and y.processtep IS NOT NULL)
Any help is greately appreciated.
AleksAleks,
The reason the query seems to be returnning records from Activities,
where the Dateinitiated column is null, is because you have specified an
Outer Join.
When you specify An Outer Join, Al records from the Outer table are
returned, even when there is no match on the other side. You actually are
NOT returning any data from Activities Table where Dateinitiated column is
null. If you look at those rows, you'll probably notice that all the field
s
from Activities table are null there...
"Aleks" wrote:
> Hi,
> I have a query that is supposed to return records and make a left join whe
re
> one field is not null, but for some reason is not working properly and
> returns the records even though they are null.
> --
> SELECT *
> FROM cases a
> left join activities as w on a.id = w.caseid AND w.Dateinitiated = (Select
> MAX(y.Dateinitiated)
> From Activities y Where y.caseid = a.id AND y.ActType ='HISTORY' and
> y.dateinitiated IS NOT NULL and y.processtep IS NOT NULL)
> --
> Any help is greately appreciated.
> Aleks
>
>
I have a query that is supposed to return records and make a left join where
one field is not null, but for some reason is not working properly and
returns the records even though they are null.
SELECT *
FROM cases a
left join activities as w on a.id = w.caseid AND w.Dateinitiated = (Select
MAX(y.Dateinitiated)
From Activities y Where y.caseid = a.id AND y.ActType ='HISTORY' and
y.dateinitiated IS NOT NULL and y.processtep IS NOT NULL)
Any help is greately appreciated.
AleksAleks,
The reason the query seems to be returnning records from Activities,
where the Dateinitiated column is null, is because you have specified an
Outer Join.
When you specify An Outer Join, Al records from the Outer table are
returned, even when there is no match on the other side. You actually are
NOT returning any data from Activities Table where Dateinitiated column is
null. If you look at those rows, you'll probably notice that all the field
s
from Activities table are null there...
"Aleks" wrote:
> Hi,
> I have a query that is supposed to return records and make a left join whe
re
> one field is not null, but for some reason is not working properly and
> returns the records even though they are null.
> --
> SELECT *
> FROM cases a
> left join activities as w on a.id = w.caseid AND w.Dateinitiated = (Select
> MAX(y.Dateinitiated)
> From Activities y Where y.caseid = a.id AND y.ActType ='HISTORY' and
> y.dateinitiated IS NOT NULL and y.processtep IS NOT NULL)
> --
> Any help is greately appreciated.
> Aleks
>
>
Monday, February 20, 2012
Problem with package creation
Hello,
I am creating a package and I have a problem with it. I want to execute a sql task that checks if there are some records with some specifications available in my table. That's very easy to do. After this step I want to send a mail message to the user, but only if there are some records available. If there aren't, it may not send a mail.
The result of the SQL task is always successfull (if there are some records or not), so I can't use the 'On Success'-workflow for it. Can you tell me how to do it, or is there another alternative for doing it?
Thx for helping me out.
TomHave the sql task execute a stored procedure that handles the logic to email or not to email (using xp_sendmail) -- that is the question :-)|||Use RASIERROR within your SQL Task
IF NOT EXISTS (SELECT * FROM myTable)
RAISERROR('',16,1)
If there are no records a RAISERROR will be done and therefore the "On Failure" will be performed.|||Thx for the solution achorozy!
I am creating a package and I have a problem with it. I want to execute a sql task that checks if there are some records with some specifications available in my table. That's very easy to do. After this step I want to send a mail message to the user, but only if there are some records available. If there aren't, it may not send a mail.
The result of the SQL task is always successfull (if there are some records or not), so I can't use the 'On Success'-workflow for it. Can you tell me how to do it, or is there another alternative for doing it?
Thx for helping me out.
TomHave the sql task execute a stored procedure that handles the logic to email or not to email (using xp_sendmail) -- that is the question :-)|||Use RASIERROR within your SQL Task
IF NOT EXISTS (SELECT * FROM myTable)
RAISERROR('',16,1)
If there are no records a RAISERROR will be done and therefore the "On Failure" will be performed.|||Thx for the solution achorozy!
Subscribe to:
Posts (Atom)