Optimize Query.

Hi,
The below query is taking lots of cpu and IO ...how can we optimize it..
SELECT Id, CorpCode, Name, CorpGroup, GroupId, LocId, StateId, AssocBranchId, WalkIn, Checker,AuthStatus, OpType, RejectReason FROM MCorporate WHERE (AuthStatus = 2 OR (AuthStatus=3 AND OpType != 1 AND OpType != 5)) AND Enabled='Y'
ThanksHi Sanjay
Much more information is needed for someone to be able to tune a query. If
it were simply a matter of looking at a query, and making suggestions, it
would be automated already and nobody would have any performance issues. :-)
At a MINIMUM, we would need:
DDL of the table and its indexes
Output of SET SHOWPLAN_ALL ON
Number of rows in the table, and data distribution for AuthStatus, OpType
and Enabled
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Sanjay" <anonymous@.discussions.microsoft.com> wrote in message
news:78614DC8-4295-456C-91AF-6355519D6019@.microsoft.com...
> Hi,
> The below query is taking lots of cpu and IO ...how can we optimize it..
> SELECT Id, CorpCode, Name, CorpGroup, GroupId, LocId, StateId,
AssocBranchId, WalkIn, Checker,AuthStatus, OpType, RejectReason FROM
MCorporate WHERE (AuthStatus = 2 OR (AuthStatus=3 AND OpType != 1 AND OpType
!= 5)) AND Enabled='Y'
> Thanks
>

optimising a table with lots of boolean fields

I have an application that reads a monitoring devices that produces 200 digital outputs every second and I would like to store them in a table. This table would get quite big fairly quickly as ultimately I would like to monitor over a hundred of these devices.

I would like to construct queries against each of the individual digital channels or combinations of them.

M first thought is to set up a table with 200 separate columns (plus others for date stamp, device ID etc) however, I am concerned that a table with 200 boolean (1-bit) fields would be an enormous waste of space if each field takes maybe one to four bytes on the hard disk to store a single bit. However, this would have the advantage of make the SQL queries more natural.

The other alternative is to create a single 200 bit field and use lots of ANDing and ORing to isolate bits to do my queries. This would make my SQL code less readable and may also cause nore hassle in the future if the inputs changed, but it would make the file size smaller.

In essence I am asking (hoping) the following : If I create a table with 200 boolean fields, does SQL server express automatically optimise the storage to make it more compact? This means that the server can mess around at the bit level and leave my higher level SQL code looking cleaner and more logical.

hi,

SQL Server can pad up to 8 bit columns together to save space, but this is another concern in your problem..

I'd actually go for a more normalized model.. you can break the 200 bit columns in a separated entity referincing the same transaction... this not only is more "elegant" and correct, but solves problem where you have to add/modify a device..

something like

SET NOCOUNT ON;

USE tempdb;

GO

CREATE TABLE dbo.Devices (

Id int NOT NULL PRIMARY KEY,

Description varchar(10) NOT NULL

);

CREATE TABLE dbo.DeviceTran (

Id int NOT NULL IDENTITY PRIMARY KEY , -- for sake of simplicity

otherData varchar(10) NULL,

TimeRecorded datetime DEFAULT GETDATE()

);

CREATE TABLE dbo.DeviceTranOutput (

Id int NOT NULL IDENTITY PRIMARY KEY , -- for sake of simplicity

IdTran int NOT NULL

CONSTRAINT fk_DeviceTran_DeviceTranOutput

FOREIGN KEY

REFERENCES dbo.DeviceTran (Id),

IdDevice int NOT NULL

CONSTRAINT fk_Devices_DeviceTranOutput

FOREIGN KEY

REFERENCES dbo.Devices (Id),

TValue bit NOT NULL DEFAULT 0

);

GO

PRINT 'available devices';

INSERT INTO dbo.Devices VALUES ( 1 , 'PDA' );

INSERT INTO dbo.Devices VALUES ( 2 , 'PBAX' );

INSERT INTO dbo.Devices VALUES ( 3 , 'PC' );

INSERT INTO dbo.Devices VALUES ( 4 , 'xxx' );

SELECT * FROM dbo.Devices;

PRINT '--';

DECLARE @.i int, @.y int, @.id int;

SET @.i = 1

WHILE @.i < 10 BEGIN

INSERT INTO dbo.DeviceTran VALUES ( 'Data ' + CONVERT(varchar, @.i), DEFAULT);

SELECT @.id = SCOPE_IDENTITY();

SET @.y = 1;

WHILE @.y < 5 BEGIN

INSERT INTO dbo.DeviceTranOutput VALUES ( @.id , @.y , (@.i + @.y) % 2);

SET @.y = @.y +1;

END

SET @.i = @.i +1

END;

GO

PRINT 'Transaction Report';

SELECT t.Id, t.otherData, t.TimeRecorded, d.Description, o.TValue

FROM dbo.DeviceTranOutput o

JOIN dbo.Devices d

ON d.Id = o.IdDevice

JOIN dbo.DeviceTran t

ON t.Id = o.IdTran;

GO

DROP TABLE dbo.DeviceTranOutput, dbo.DeviceTran, dbo.Devices;

resulting in something likeTransaction Report
Id otherData TimeRecorded Description TValue
-- - -- --
1 Data 1 2006-07-17 18:23:01.717 PDA 0
1 Data 1 2006-07-17 18:23:01.717 PBAX 1
1 Data 1 2006-07-17 18:23:01.717 PC 0
1 Data 1 2006-07-17 18:23:01.717 xxx 1
2 Data 2 2006-07-17 18:23:01.717 PDA 1
2 Data 2 2006-07-17 18:23:01.717 PBAX 0
2 Data 2 2006-07-17 18:23:01.717 PC 1
2 Data 2 2006-07-17 18:23:01.717 xxx 0
3 Data 3 2006-07-17 18:23:01.717 PDA 0
3 Data 3 2006-07-17 18:23:01.717 PBAX 1
-- result abrdiged..

storage will be better used, you have some penality in both reporting and inserting, as JOIN operations are involved, but you get far better design you can expand/modify with no worries about modifications in the device pattern...

regards

|||Thanks for that.

I'll probably adopt your suggestion.

In order to save space, I am considering only storing the ocasions when the digital values change (rather than every time step).
Using your method, I don't need to store every channel every time so that is another reason in favour of it.

It does lead to another question though..

Taking your example: say a DeviceTran record is generate every timestep for the benefit of some analogue channel 'X' that is continuously changing.
Take a digital channel called 'Y' that is only recorded every time it changes.
Then, if I do an outer join to gather all the data at every time step, I might get something like this,,,

Id TimeRecorded X Y
-- -- --
1 2006-07-17 18:23:01.000 1.0 false
2 2006-07-17 18:23:02.000 1.1 null
3 2006-07-17 18:23:03.000 1.2 null
4 2006-07-17 18:23:04.000 1.1 true
5 2006-07-17 18:23:05.000 1.4 null
6 2006-07-17 18:23:06.000 1.5 null
7 2006-07-17 18:23:07.000 1.2 false
8 2006-07-17 18:23:08.000 0.9 null
9 2006-07-17 18:23:09.000 0.8 nullFor a particular value of DATETIME I would like to get the most recent
record for Y.
A straight outer join would just show null values for those not present.
Is there a clever way of joining but using the 'last' value based on
datetime or id.

Regards
|||

hi,

I'm not sure I fully understand your requirements..

I think you mean you just avoid to insert repeted values as following..

SET NOCOUNT ON;

SET NOCOUNT ON;

USE tempdb;

GO

CREATE TABLE dbo.Devices (

Id int NOT NULL PRIMARY KEY,

Description varchar(10) NOT NULL

);

CREATE TABLE dbo.DeviceTran (

Id int NOT NULL IDENTITY PRIMARY KEY ,

otherData varchar(10) NULL,

TimeRecorded datetime NOT NULL DEFAULT GETDATE()

);

CREATE TABLE dbo.DeviceTranOutput (

Id int NOT NULL IDENTITY PRIMARY KEY ,

IdTran int NOT NULL

CONSTRAINT fk_DeviceTran_DeviceTranOutput

FOREIGN KEY

REFERENCES dbo.DeviceTran (Id),

IdDevice int NOT NULL

CONSTRAINT fk_Devices_DeviceTranOutput

FOREIGN KEY

REFERENCES dbo.Devices (Id),

TValue bit NULL

);

GO

DECLARE @.t datetime, @.id int, @.dev int;

SELECT @.dev = 1, @.t = GETDATE();

INSERT INTO dbo.Devices VALUES ( @.dev , 'PBAX' );

INSERT INTO dbo.DeviceTran VALUES ( 'Data Pbax' , @.t);

SELECT @.id = SCOPE_IDENTITY();

INSERT INTO dbo.DeviceTranOutput VALUES ( @.id , @.dev , 0);

WAITFOR DELAY '00:00:01'

SELECT @.t = GETDATE();

INSERT INTO dbo.DeviceTran VALUES ( 'Data Pbax' , @.t);

SELECT @.id = SCOPE_IDENTITY();

--INSERT INTO dbo.DeviceTranOutput VALUES ( @.id , @.dev , 0);

WAITFOR DELAY '00:00:01'

SELECT @.t = GETDATE();

INSERT INTO dbo.DeviceTran VALUES ( 'Data Pbax' , @.t);

SELECT @.id = SCOPE_IDENTITY();

--INSERT INTO dbo.DeviceTranOutput VALUES ( @.id , @.dev , 0);

WAITFOR DELAY '00:00:01'

SELECT @.t = GETDATE();

INSERT INTO dbo.DeviceTran VALUES ( 'Data Pbax' , @.t);

SELECT @.id = SCOPE_IDENTITY();

INSERT INTO dbo.DeviceTranOutput VALUES ( @.id , @.dev , 1);

WAITFOR DELAY '00:00:01'

SELECT @.t = GETDATE();

INSERT INTO dbo.DeviceTran VALUES ( 'Data Pbax' , @.t);

SELECT @.id = SCOPE_IDENTITY();

--INSERT INTO dbo.DeviceTranOutput VALUES ( @.id , @.dev , 1);

PRINT 'Transaction Report';

PRINT '';

PRINT 'IdDevice is NULL, you can''t directly reference';

PRINT 'the devices, you have to resort on ''specific'' queries';

SELECT t.Id, t.otherData, t.TimeRecorded, d.Description,

(SELECT TOP 1 o2.TValue

FROM dbo.DeviceTranOutput o2

WHERE o2.IdDevice = d.Id

AND o2.IdTran <= t.Id

ORDER BY o2.Id DESC) AS TValue

FROM dbo.DeviceTranOutput o

JOIN dbo.Devices d ON d.Id = o.IdDevice

RIGHT JOIN dbo.DeviceTran t ON t.Id = o.IdTran

ORDER BY t.TimeRecorded;

GO

PRINT 'as passing a [@.DeviceId] as a parameter';

DECLARE @.DeviceId int;

DECLARE @.DeviceDescription varchar(10);

SELECT @.DeviceId = 1;

SELECT @.DeviceDescription = Description

FROM dbo.Devices

WHERE Id = @.DeviceId;

SELECT t.Id, t.otherData, t.TimeRecorded,

@.DeviceDescription AS [Description],

(SELECT TOP 1 o2.TValue

FROM dbo.DeviceTranOutput o2

WHERE o2.IdDevice = @.DeviceId

AND o2.IdTran <= t.Id

ORDER BY o2.Id DESC) AS TValue

FROM dbo.DeviceTranOutput o

JOIN dbo.Devices d ON d.Id = o.IdDevice

RIGHT JOIN dbo.DeviceTran t ON t.Id = o.IdTran;

GO

DROP TABLE dbo.DeviceTranOutput, dbo.DeviceTran, dbo.Devices;

--<-

Transaction Report
IdDevice is NULL, you can't directly reference

the devices, you have to resort on 'specific' queries

Id otherData TimeRecorded Description TValue

-- - -- --

1 Data Pbax 2006-07-18 22:56:51.810 PBAX 0

2 Data Pbax 2006-07-18 22:56:52.827 NULL NULL

3 Data Pbax 2006-07-18 22:56:53.827 NULL NULL

4 Data Pbax 2006-07-18 22:56:54.827 PBAX 1

5 Data Pbax 2006-07-18 22:56:55.827 NULL NULL

as passing a [@.DeviceId] as a parameter

Id otherData TimeRecorded Description TValue

-- - -- --

1 Data Pbax 2006-07-18 22:56:51.810 PBAX 0

2 Data Pbax 2006-07-18 22:56:52.827 PBAX 0

3 Data Pbax 2006-07-18 22:56:53.827 PBAX 0

4 Data Pbax 2006-07-18 22:56:54.827 PBAX 1

5 Data Pbax 2006-07-18 22:56:55.827 PBAX 1

but my idea is you'll go into troubles both at insert time, as you have to check if the current value is the same as the last one, and later at query time, as you miss some references..
you can scan for an older value in dbo.DeviceTranOutput of a previous transaction, but you miss the IdDevice value... if you query for a specified device then it's allright, as you pass the IdDevice as a parameter, which becames a constant, but a general purpose query to list all transactions (orderd by TimeRecorded and IdDevice) becames heavy, for every row ...
at insert time this is a heavy load as well as instead of just inserting you have to check, and this can cost a lot in real time apps..
considering you're collecting data in quiet real time, I'd go for the quicker (is it english?) way to pump data in, without trigger to filter out repeated values...

more, transactionally, it breaks a rule of atomicity of a row, as it depends on the values of previous rows... it makes all the design trickier, and of corse coding as well.. my $0.02..

regards

|||Thanks for that.
You have understood my requirements exactly.

I was hoping to save disk space by not storing all the values for a timestep that haven't changed. But you are right about breaking the rule of atomicity, because the value of a particular column now depends on its 'last' non-null value. I hadn't thought of it like that.

Whatever the gains I make in disk space I have to seriously consider the penalty in SQL complexity. Trying to extract the 'current' value for just one or maybe a handful of channels can be done (as you have shown above) but a generic query to return values for all channels might be very complex/slow.

I'll go away and think some more on it.

Thanks once again.

Regards

optimal database design

What is more efficient for a database design - a lot of tables with only a
few records or a few tables with lots of records.
I'm starting a new site and each user will have numerous records but I'm not
sure whether to have a few very large tables (over 100,000 rows) or start a
new table for each user which would result in approx 1500 tables most of
which would be the same table design with different rows.
I'm using SQL2000.
I guess this is quite a basic question, but I'm a bit unsure.
Any references anyone could point me too as well.
Thx
Database design is based on the analysis of data entities of really world,
rarely on the amount/quantity of an entity. Usually an entity is translated
into a table in database, say, you have a database with Customers table,
Orders table, Order Details table... You do not create many customer tables
just because of too many customers' records, it is not optimal, it is simply
wrong. BTW, a table with a million rows may not be as that big as you
thought, unless the table has a lot columns and the columns' size is big.
For SQL Server, there is not problem to handle a database with a few
million-row tables with regular columns (say, a couple of dozen columns,
mostly numbers and short texts)
"jwk" <jwk@.discussions.microsoft.com> wrote in message
news:D86EC2E3-A808-4809-AEF6-61F3618E0EEB@.microsoft.com...
> What is more efficient for a database design - a lot of tables with only a
> few records or a few tables with lots of records.
> I'm starting a new site and each user will have numerous records but I'm
not
> sure whether to have a few very large tables (over 100,000 rows) or start
a
> new table for each user which would result in approx 1500 tables most of
> which would be the same table design with different rows.
> I'm using SQL2000.
> I guess this is quite a basic question, but I'm a bit unsure.
> Any references anyone could point me too as well.
> Thx
>
|||100K rows is not a lot by any means in Sql Server. Trying to manage a
different table for each use sounds like a nightmare that you should wake up
from as soon as possible.
Andrew J. Kelly SQL MVP
"jwk" <jwk@.discussions.microsoft.com> wrote in message
news:D86EC2E3-A808-4809-AEF6-61F3618E0EEB@.microsoft.com...
> What is more efficient for a database design - a lot of tables with only a
> few records or a few tables with lots of records.
> I'm starting a new site and each user will have numerous records but I'm
> not
> sure whether to have a few very large tables (over 100,000 rows) or start
> a
> new table for each user which would result in approx 1500 tables most of
> which would be the same table design with different rows.
> I'm using SQL2000.
> I guess this is quite a basic question, but I'm a bit unsure.
> Any references anyone could point me too as well.
> Thx
>
|||A new table for each user doesn't sound like a very sensible design.
Each table should represent a single entity - that normally means one
table for all things that have a common set of attributes (columns).
100,000 rows is very small in SQL Server terms (to most people anyway)
but whatever the size, start with a normalized logical design - then
think about performance considerations if performance evaluations
demonstrate you may have problems.
David Portas
SQL Server MVP

optimal database design

What is more efficient for a database design - a lot of tables with only a
few records or a few tables with lots of records.
I'm starting a new site and each user will have numerous records but I'm not
sure whether to have a few very large tables (over 100,000 rows) or start a
new table for each user which would result in approx 1500 tables most of
which would be the same table design with different rows.
I'm using SQL2000.
I guess this is quite a basic question, but I'm a bit unsure.
Any references anyone could point me too as well.
ThxDatabase design is based on the analysis of data entities of really world,
rarely on the amount/quantity of an entity. Usually an entity is translated
into a table in database, say, you have a database with Customers table,
Orders table, Order Details table... You do not create many customer tables
just because of too many customers' records, it is not optimal, it is simply
wrong. BTW, a table with a million rows may not be as that big as you
thought, unless the table has a lot columns and the columns' size is big.
For SQL Server, there is not problem to handle a database with a few
million-row tables with regular columns (say, a couple of dozen columns,
mostly numbers and short texts)
"jwk" <jwk@.discussions.microsoft.com> wrote in message
news:D86EC2E3-A808-4809-AEF6-61F3618E0EEB@.microsoft.com...
> What is more efficient for a database design - a lot of tables with only a
> few records or a few tables with lots of records.
> I'm starting a new site and each user will have numerous records but I'm
not
> sure whether to have a few very large tables (over 100,000 rows) or start
a
> new table for each user which would result in approx 1500 tables most of
> which would be the same table design with different rows.
> I'm using SQL2000.
> I guess this is quite a basic question, but I'm a bit unsure.
> Any references anyone could point me too as well.
> Thx
>|||100K rows is not a lot by any means in Sql Server. Trying to manage a
different table for each use sounds like a nightmare that you should wake up
from as soon as possible.
Andrew J. Kelly SQL MVP
"jwk" <jwk@.discussions.microsoft.com> wrote in message
news:D86EC2E3-A808-4809-AEF6-61F3618E0EEB@.microsoft.com...
> What is more efficient for a database design - a lot of tables with only a
> few records or a few tables with lots of records.
> I'm starting a new site and each user will have numerous records but I'm
> not
> sure whether to have a few very large tables (over 100,000 rows) or start
> a
> new table for each user which would result in approx 1500 tables most of
> which would be the same table design with different rows.
> I'm using SQL2000.
> I guess this is quite a basic question, but I'm a bit unsure.
> Any references anyone could point me too as well.
> Thx
>|||A new table for each user doesn't sound like a very sensible design.
Each table should represent a single entity - that normally means one
table for all things that have a common set of attributes (columns).
100,000 rows is very small in SQL Server terms (to most people anyway)
but whatever the size, start with a normalized logical design - then
think about performance considerations if performance evaluations
demonstrate you may have problems.
David Portas
SQL Server MVP
--

optimal database design

What is more efficient for a database design - a lot of tables with only a
few records or a few tables with lots of records.
I'm starting a new site and each user will have numerous records but I'm not
sure whether to have a few very large tables (over 100,000 rows) or start a
new table for each user which would result in approx 1500 tables most of
which would be the same table design with different rows.
I'm using SQL2000.
I guess this is quite a basic question, but I'm a bit unsure.
Any references anyone could point me too as well.
ThxDatabase design is based on the analysis of data entities of really world,
rarely on the amount/quantity of an entity. Usually an entity is translated
into a table in database, say, you have a database with Customers table,
Orders table, Order Details table... You do not create many customer tables
just because of too many customers' records, it is not optimal, it is simply
wrong. BTW, a table with a million rows may not be as that big as you
thought, unless the table has a lot columns and the columns' size is big.
For SQL Server, there is not problem to handle a database with a few
million-row tables with regular columns (say, a couple of dozen columns,
mostly numbers and short texts)
"jwk" <jwk@.discussions.microsoft.com> wrote in message
news:D86EC2E3-A808-4809-AEF6-61F3618E0EEB@.microsoft.com...
> What is more efficient for a database design - a lot of tables with only a
> few records or a few tables with lots of records.
> I'm starting a new site and each user will have numerous records but I'm
not
> sure whether to have a few very large tables (over 100,000 rows) or start
a
> new table for each user which would result in approx 1500 tables most of
> which would be the same table design with different rows.
> I'm using SQL2000.
> I guess this is quite a basic question, but I'm a bit unsure.
> Any references anyone could point me too as well.
> Thx
>|||100K rows is not a lot by any means in Sql Server. Trying to manage a
different table for each use sounds like a nightmare that you should wake up
from as soon as possible.
--
Andrew J. Kelly SQL MVP
"jwk" <jwk@.discussions.microsoft.com> wrote in message
news:D86EC2E3-A808-4809-AEF6-61F3618E0EEB@.microsoft.com...
> What is more efficient for a database design - a lot of tables with only a
> few records or a few tables with lots of records.
> I'm starting a new site and each user will have numerous records but I'm
> not
> sure whether to have a few very large tables (over 100,000 rows) or start
> a
> new table for each user which would result in approx 1500 tables most of
> which would be the same table design with different rows.
> I'm using SQL2000.
> I guess this is quite a basic question, but I'm a bit unsure.
> Any references anyone could point me too as well.
> Thx
>|||A new table for each user doesn't sound like a very sensible design.
Each table should represent a single entity - that normally means one
table for all things that have a common set of attributes (columns).
100,000 rows is very small in SQL Server terms (to most people anyway)
but whatever the size, start with a normalized logical design - then
think about performance considerations if performance evaluations
demonstrate you may have problems.
--
David Portas
SQL Server MVP
--

optimal database design

What is more efficient for a database design - a lot of tables with only a few records or a few tables with lots of records.

I'm starting a new site and each user will have numerous records but I'm not sure whether to have a few very large tables (over 100,000 rows) or start a new table for each user which would result in approx 1500 tables most of which would be the same table design with different rows.

I'm using SQL2000.

I guess this is quite a basic question, but I'm a bit unsure.

Any references anyone could point me too as well.

ThxThe number of rows is dependent upon a lot of things. The number of COLUMNS per row is more likely indicitive of good design. I would never create a new table for each user. That is why there are indexes.

This is an excellent book on Database Design:

Designing Effective Database Systems
By Rebecca M. Riordan.
Published by Addison Wesley Professional.

http://www.awprofessional.com/title/0321290933|||I agree with Douglas. From a reporting and maintenance standpoint, fewer tables will be much better. Implementing a different set of tables for each user will lead to many, many problems down the road.|||I had the same doubts and I was told to read about Database Normalization. It could help you too