Here's a little background on the query. I have a list of documents by an id number in one table and the description of the sheets in another table. It's a one to many relationship, so for each description, there may be multiple entries in the documents table that it applies to. For example:
Descriptions table:
ID | Title
Doc1 | Document 1
Doc2 | Document 2
Documents table:
ID | Parent
Doc1 | 10400
Doc2 | 10400
Doc1 | 20189
Doc3 | 20189
View:
ID | Parent | Description
Doc1 | 10400 | Document 1
Doc2 | 10400 | Document 2
Doc1 | 20189 | Document 1
Doc3 | 20189 | (null)
So the query I am using uses a left join to combine the data from the one table into the other. There might not be an entry for the description, so for some Document entries, the description field may be blank. For some reason, certain queries take about 2 minutes longer than others who retreive 5 times the information.
In SQL Manager, is says "Executing Query. Waiting for response from data source." After about 20 seconds it says "Retrieving Data..." then about a minute later, it finally comes up with the data. I can select another parent that has a lot more items and it comes up in about 3 seconds max.
It's running on SQL Server 2005 with 2GB of RAM.
Any suggestions on tracking down the reason for the slowness would be great.
Thanks in advance!!!
-DanPost the query you are executing.|||SELECT
dbo.Table1.ItemType,
dbo.Table1.ItemLabel,
dbo.Table1.ItemParent,
dbo.Table1.ItemID,
dbo.Table1.ProjectID,
dbo.Table1.ItemDate,
dbo.Table1.Active,
dbo.Table1.ItemBaseLabel,
dbo.Table2.ItemDescription
FROM dbo.Table1 LEFT OUTER JOIN dbo.Table2
ON dbo.Table1.ProjectID = dbo.Table2.ProjectID
AND dbo.Table1.ItemBaseLabel = dbo.Table2.ItemBaseLabel
Here you go!
-Dan|||where are the indices placed? have you looked at the execution plan?|||That's weird... I just checked the indices on the base table and there are three. One is By ItemID. Another is by ItemLabel and another is by ItemLabel, ItemParent, Item ID, and another field. Could they be throwing off the way the data is being stored?
There are no indices on the view and I cannot add one. It comes up with an error saying that it can't add an index because it's not schema based. Please, bear with me - I'm new to 2005. This was originally stored on MSSQL 7.0 and it worked fine there.
Thanks!
-Dan|||typically you want indices on search conditions, primary keys (there is usually a clustered one there) and the foriegn keys but you should examine the execution plan first to make sure this will help. if they are already there, make sure they are not fragmented, the statistics are up to date, and then if this is a sp, recompile it. I am not working with 2K5 just yet.|||I checked the indices and everything seems to be in order. I also tried it through a stored procedure and it still takes way too long.
Any other ideas?
-Dan|||for the third time... have you looked at the execution plan?|||Sorry. The execution plan said that a table scan on the dbo.Table2 was 100% cost. So I looked at the fields it was referencing and added a column to the Table2 index and then re-ran the execution plan and it was more divided among the tasks, now with 2 index scans.
Went back to the query and now it's blazing fast.
Apparently my listening skills are about as good as my databasing skills...
Thanks Thrasymachus. :)
-Dan
Showing posts with label number. Show all posts
Showing posts with label number. Show all posts
Friday, March 30, 2012
Monday, March 26, 2012
Interim Summing every x number of rows
Using SQL Server 2005 Standard & SQL Server Reporting Services.
First off, here is the application.
An airport baggage handling system distributes bags using multiple conveyors. Bag counts are logged every 15 minutes. There is a count for each conveyor. Example Log Table layout is as follows (The TIME column is DateTime, the Convx columns are TinyInt)Time Conv1 Conv2 Conv3 Conv4 Conv5 Conv6
i 3 2 3 4 2 1
i+15min 2 3 4 2 2 2
i+30min etc.....................
i+45min
i+60min
etc...
The management team wants a throughput report which will take the following parameters in order to filter the results:
Begin Date End Date Time Interval (selectable as 15mins, 30 mins, 45mins, 60 mins and Daily)My question is this. Given that my raw data has 1 row for every 15 minutes, if they select 60 minutes as their interval I need to run the query with the start and end dates but Sum every 4 rows and display it as 1 row, likewise if they select 30 minute interval, I need to sum every 2 rows. How do I run a query and SUM the Conv count data for every x number of rows and use the 1st TIME value in the returned x row summary?
Thanks for your help and let me know if I need to clarify anything
For this kind of question / problem, it is very very very helpful to post the estructure of the tables, including constraints and indexes, sample data (insert statements) and expected result.
AMB
|||OK, I'm a newbie at all of this so I will try and give you what you asked for:
In the Tables below, RecordID is my Key, Identity field, Increment 1, seed 1 - Data Type Int.
Conv columns are all SmallInt and TimeStampVal is DateTime with DefaultValue of (getdate()) to log the time when the record is inserted. 1 Record will be insertedevery 15 mins via an OPC Server.
Example of table including sample data - for easy explanation of Math I have presumed that each conveyor will have a throughput of 2 bags every 15 minutes
Expected result Data for Query ran with Interval parameter set to 15 mins will be identical to the table above (without the RecordID column.)
Expected result Data for Query ran with Interval Parameter set to 30 Mins
Expected Result Set for Query Ran with Interval Parameter set to 60 Mins
Hope this helps explain it a little more, if not let me know how I can clarify.
Thanks for your time.
|||This is a good start, however, given the information in this format, anyone wanting to help would need to spend some time creating the create table statements and insert statements before they can start helping you. You will get a lot more help if you provide the create table and insert statements like this.
Code Snippet
CREATE TABLE BagCounts
(
RecordID INT IDENTITY (1,1) PRIMARY KEY,
Conv1 smallint,
Conv2 smallint,
Conv3 smallint,
TimeStampVal DateTime
)
GO
INSERT INTO BagCounts (Conv1, Conv2, Conv3, TimeStampVal)
SELECT 2, 2, 2, '2007-06-29 07:00:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 07:15:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 07:30:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 07:45:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 08:00:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 08:15:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 08:30:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 08:45:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 09:00:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 09:15:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 09:30:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 09:45:00.000'
GO
I will see if I can come up with a procedure to show you how to get what you want.
|||Try:
Code Snippet
createtable #t (
RecordID intnotnulluniqueclustered,
Conv1 smallintnotnull,
Conv2 smallintnotnull,
Conv3 smallintnotnull,
TimeStampVal datetimenotnull
)
go
setnocounton
insertinto #t values(1, 2, 2, 2,'2007-06-29 07:00:00.000')
insertinto #t values(2, 2, 2, 2,'2007-06-29 07:15:00.000')
insertinto #t values(3, 2, 2, 2,'2007-06-29 07:30:00.000')
insertinto #t values(4, 2, 2, 2,'2007-06-29 07:45:00.000')
insertinto #t values(5, 2, 2, 2,'2007-06-29 08:00:00.000')
insertinto #t values(6, 2, 2, 2,'2007-06-29 08:15:00.000')
insertinto #t values(7, 2, 2, 2,'2007-06-29 08:30:00.000')
insertinto #t values(8, 2, 2, 2,'2007-06-29 08:45:00.000')
insertinto #t values(9, 2, 2, 2,'2007-06-29 09:00:00.000')
insertinto #t values(10, 2, 2, 2,'2007-06-29 09:15:00.000')
insertinto #t values(11, 2, 2, 2,'2007-06-29 09:30:00.000')
insertinto #t values(12, 2, 2, 2,'2007-06-29 09:45:00.000')
setnocountoff
go
declare @.sd datetime
declare @.interval int
set @.sd = '2007-06-29 07:00:00.000'
set @.interval = 30
;with cte_1
as
(
select
*,
datediff(minute, @.sd, TimeStampVal)/ @.interval as grp
from
#t
)
select
sum(Conv1)as Conv1,
sum(Conv2)as Conv2,
sum(Conv3)as Conv3,
min(TimeStampVal)as TimeStampVal
from
cte_1
group by
grp
order by
min(TimeStampVal)
go
droptable #t
go
AMB
|||Hunchback, Thanks for the solution, it was just what I was looking for. It took me a while to look some stuff up to figure out what you were doing (I've only been using SQL Server for around 2 months now).
I do have a quick question for clarification though if you would indulge me:
In this part of the code i think you are creating a Common Table Expression (cte_1) which basically replicates the original table and adds a new column onto it called grp.
Code Snippet
with cte_1
as
(
select
*,
datediff(minute, @.sd, TimeStampVal) / @.interval as grp
from
#t
)
I had never heard of CTE's until now so please forgive any dumb questions but why do you not have to specify a datatype for the grp column? I know that it somehow defaults to an int from playing with your codesnippet to view the entire cte_1 table but why does it do this when the values returned from the datediff/@.interval function are floating point?
By the way your solution is pure genious - I am still trying to get my brain thinking in t-sql, some of the problems and solutions I am reading on this forum are really opening my eyes as to what is possible.
Thanks again
Colin
|||Hi Colin,
Glad it helped.
> why do you not have to specify a datatype for the grp column? I know that it somehow defaults to an int from playing with your codesnippet to view
> the entire cte_1 table but why does it do this when the values returned from the datediff/@.interval function are floating point?
The function DATEDIFF returns int, the variable @.interval is int and the division of integers yields integer.
select 1 / 2, 1 / 2.
go
I will suggest, if you want to learn more T-SQL, to get the books:
- Inside SQL server 2005: T-SQL Querying
- Inside SQL server 2005: T-SQL Programming
you will not regret having those books.
AMB
Friday, March 23, 2012
Interim Summing every x number of rows
Using SQL Server 2005 Standard & SQL Server Reporting Services.
First off, here is the application.
An airport baggage handling system distributes bags using multiple conveyors. Bag counts are logged every 15 minutes. There is a count for each conveyor. Example Log Table layout is as follows (The TIME column is DateTime, the Convx columns are TinyInt)Time Conv1 Conv2 Conv3 Conv4 Conv5 Conv6
i 3 2 3 4 2 1
i+15min 2 3 4 2 2 2
i+30min etc.....................
i+45min
i+60min
etc...
The management team wants a throughput report which will take the following parameters in order to filter the results:
Begin Date End Date Time Interval (selectable as 15mins, 30 mins, 45mins, 60 mins and Daily)My question is this. Given that my raw data has 1 row for every 15 minutes, if they select 60 minutes as their interval I need to run the query with the start and end dates but Sum every 4 rows and display it as 1 row, likewise if they select 30 minute interval, I need to sum every 2 rows. How do I run a query and SUM the Conv count data for every x number of rows and use the 1st TIME value in the returned x row summary?
Thanks for your help and let me know if I need to clarify anything
For this kind of question / problem, it is very very very helpful to post the estructure of the tables, including constraints and indexes, sample data (insert statements) and expected result.
AMB
|||OK, I'm a newbie at all of this so I will try and give you what you asked for:
In the Tables below, RecordID is my Key, Identity field, Increment 1, seed 1 - Data Type Int.
Conv columns are all SmallInt and TimeStampVal is DateTime with DefaultValue of (getdate()) to log the time when the record is inserted. 1 Record will be insertedevery 15 mins via an OPC Server.
Example of table including sample data - for easy explanation of Math I have presumed that each conveyor will have a throughput of 2 bags every 15 minutes
Expected result Data for Query ran with Interval parameter set to 15 mins will be identical to the table above (without the RecordID column.)
Expected result Data for Query ran with Interval Parameter set to 30 Mins
Expected Result Set for Query Ran with Interval Parameter set to 60 Mins
Hope this helps explain it a little more, if not let me know how I can clarify.
Thanks for your time.
|||This is a good start, however, given the information in this format, anyone wanting to help would need to spend some time creating the create table statements and insert statements before they can start helping you. You will get a lot more help if you provide the create table and insert statements like this.
Code Snippet
CREATE TABLE BagCounts
(
RecordID INT IDENTITY (1,1) PRIMARY KEY,
Conv1 smallint,
Conv2 smallint,
Conv3 smallint,
TimeStampVal DateTime
)
GO
INSERT INTO BagCounts (Conv1, Conv2, Conv3, TimeStampVal)
SELECT 2, 2, 2, '2007-06-29 07:00:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 07:15:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 07:30:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 07:45:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 08:00:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 08:15:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 08:30:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 08:45:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 09:00:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 09:15:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 09:30:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 09:45:00.000'
GO
I will see if I can come up with a procedure to show you how to get what you want.
|||Try:
Code Snippet
create table #t (
RecordID int not null unique clustered,
Conv1 smallint not null,
Conv2 smallint not null,
Conv3 smallint not null,
TimeStampVal datetime not null
)
go
set nocount on
insert into #t values(1, 2, 2, 2, '2007-06-29 07:00:00.000')
insert into #t values(2, 2, 2, 2, '2007-06-29 07:15:00.000')
insert into #t values(3, 2, 2, 2, '2007-06-29 07:30:00.000')
insert into #t values(4, 2, 2, 2, '2007-06-29 07:45:00.000')
insert into #t values(5, 2, 2, 2, '2007-06-29 08:00:00.000')
insert into #t values(6, 2, 2, 2, '2007-06-29 08:15:00.000')
insert into #t values(7, 2, 2, 2, '2007-06-29 08:30:00.000')
insert into #t values(8, 2, 2, 2, '2007-06-29 08:45:00.000')
insert into #t values(9, 2, 2, 2, '2007-06-29 09:00:00.000')
insert into #t values(10, 2, 2, 2, '2007-06-29 09:15:00.000')
insert into #t values(11, 2, 2, 2, '2007-06-29 09:30:00.000')
insert into #t values(12, 2, 2, 2, '2007-06-29 09:45:00.000')
set nocount off
go
declare @.sd datetime
declare @.interval int
set @.sd = '2007-06-29 07:00:00.000'
set @.interval = 30
;with cte_1
as
(
select
*,
datediff(minute, @.sd, TimeStampVal) / @.interval as grp
from
#t
)
select
sum(Conv1) as Conv1,
sum(Conv2) as Conv2,
sum(Conv3) as Conv3,
min(TimeStampVal) as TimeStampVal
from
cte_1
group by
grp
order by
min(TimeStampVal)
go
drop table #t
go
AMB
|||Hunchback, Thanks for the solution, it was just what I was looking for. It took me a while to look some stuff up to figure out what you were doing (I've only been using SQL Server for around 2 months now).
I do have a quick question for clarification though if you would indulge me:
In this part of the code i think you are creating a Common Table Expression (cte_1) which basically replicates the original table and adds a new column onto it called grp.
Code Snippet
with cte_1
as
(
select
*,
datediff(minute, @.sd, TimeStampVal) / @.interval as grp
from
#t
)
I had never heard of CTE's until now so please forgive any dumb questions but why do you not have to specify a datatype for the grp column? I know that it somehow defaults to an int from playing with your codesnippet to view the entire cte_1 table but why does it do this when the values returned from the datediff/@.interval function are floating point?
By the way your solution is pure genious - I am still trying to get my brain thinking in t-sql, some of the problems and solutions I am reading on this forum are really opening my eyes as to what is possible.
Thanks again
Colin
|||Hi Colin,
Glad it helped.
> why do you not have to specify a datatype for the grp column? I know that it somehow defaults to an int from playing with your codesnippet to view
> the entire cte_1 table but why does it do this when the values returned from the datediff/@.interval function are floating point?
The function DATEDIFF returns int, the variable @.interval is int and the division of integers yields integer.
select 1 / 2, 1 / 2.
go
I will suggest, if you want to learn more T-SQL, to get the books:
- Inside SQL server 2005: T-SQL Querying
- Inside SQL server 2005: T-SQL Programming
you will not regret having those books.
AMB
Subscribe to:
Posts (Atom)