OpenXML Identity Insert: optimising

Showing posts with label optimising. Show all posts

Monday, March 19, 2012

Optimising Select statements which has a LIKE where clause.

Hi all

I have been doing some development work in a large VB6 application. I have updated the search capabilities of the application to allow the user to search on partial addresses as the existing search routine only allowed you to search on the whole line of the address.

Simple change to the stored procedure (this is just an example not the real stored proc):

From:
Select Top 3000 * from TL_ClientAddresses with(nolock) Where strPostCode = W1 ABC
To:
Select Top 3000 * from TL_ClientAddresses with(nolock) Where strPostCode LIKE W1%

Now this is when things went a bit crazy. I know the implications of using with(nolock). But seeing the code is only using the ID field to get the required row, and the database is a live database with hundreds of users at any one time (some updating), I think a dirty read is ok in this routine, as I dont want SQL to create a shared lock.

Anyway my problem is this. After the change, the search now created a Shared Lock which sometimes locks out some of the live users updating the system. The Select is also extremely SLOW. It took about 5 minutes to search just over a million records (locking the database during the search, and giving my manager good reason to shout abuse at me). So I checked the indexes. I had an index set on:

strAddressLine1, strAddressLine2, strAddressLine3, strAddressLine4, strPostCode.

So I created an index just for the strPostCode (non clustered).

This had no change to the Like select what so ever. So I am now stuck.

1) Is there another way to search for part of a text field in SQL.
2) Does Like comparison use the index in any way? If so how do I set this index up?
3) Can I stop a Shared Lock being created when I do a like select?
4) Do you have any good comebacks I could tell the boss after his next outburst of abuse (please not so bad that he sacks me).

Any advice truly appreciated.1. I have been working on smaller database systems the last couple of years but as for number 1 try changing the query to "=" with a wild card "%" in the QA with the show execution plan on and see if the index is being used.

2. I seem to remember that the like operator cancels the index. To check this I would execute the query in the QA and check the execution plan.

3. Don't know off the top of my head.

4. Tell your boss that software like the people who create it are imperfect things.|||Like is one of those "fuzzy" things and does not use indexes. Postcodes are notoriously difficult to search on...

In your example you give "W1%"

realise that this will return W12 etc

I in the time I had to do this tried to Narrow the user down to the local as in W1
W12 etc

Alternative prospect here

Split your Postcode into two fields (I know it sounds wierd) but then you can do an = rather than a like and an index can be used!|||I'd try to run the query in the Query Analyzer. In the form that you posted the query, it ought to use the PostCode index. It ought to be able to ride the index as far as the first wildcard (percent sign in this case). If you examine the query plan, it might give you some idea of where the problem is.

-PatP|||I get an index seek with a bookmark lookup

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(strPostCode varchar(10), Col2 int)
GO

INSERT INTO myTable99(strPostCode, Col2)
SELECT 'W1Me',1 UNION ALL
SELECT 'W2Me',2 UNION ALL
SELECT 'W3Me',3 UNION ALL
SELECT 'W4Me',4
GO

CREATE INDEX myTable99_strPostCode ON myTable99(strPostCode)
GO

--[CTRL]+K
Select Top 3000 * from myTable99 with(nolock) Where strPostCode LIKE 'W1%'
GO

SET NOCOUNT OFF
GO|||Yeah, but that's using the code that they posted, which might or might not generate the same plan as the code they are actually using ;) Not that I've ever been burned by different code being posted than what is actually run before, I just read about it in this book once...

-PatP|||First like to thank everyone who replied. Very much appreciated.

Unfortunately I am still not much closer at finding a solution. I have done the execution plan thing although I can see a index seek, I dont think the index I have created is being used (but I am not sure).

If I delete the index, then the search time on about 500,000 records (Im testing from a subset of the total number of rows in the table) is about the same as when the index is present. If I do a = search, then with an index runs in less than a second and without takes much longer.

Anyway if anyone has any other ideas or a total different approach I can take then please let me know. Also if anyone knows of any websites or books that look into like selects more deeply than just giving you the syntax, like every book and site I have found, then that would be good too.

Regards

Eamon.|||1. Try use index hint:
Select Top 3000 * from TL_ClientAddresses with(nolock, INDEX (your_index_name)) Where strPostCode LIKE W1%
2. Try update statistics:
EXEC sp_updatestats
3. Try change query:
Select Top 3000 * from TL_ClientAddresses with(nolock)
Where strPostCode >= W1 AND strPostCode =< W1z
4. If your query fetch more then 20% table then optimizer don't use index|||mwolf! Your a Star!!!

Select Top 3000 * from TL_ClientAddresses with(nolock, INDEX (your_index_name)) Where strPostCode LIKE W1%

Works a treat!!! Gone from over a minute down to 6 seconds just by adding the 'INDEX()' statement.

I think that's the answer to my problem, Thankyou very much!!!

Regards

Eamon.

Optimising Query based on Views

quote:

> I would be grateful for some advice.
> I have a query which selects from a view which, in turn, is based on three
> other view. I want to optimise the query.
> The query is built dynamically so cannot be made into a stored procedure.

quote:

> it worth my while making the main view into a user-defined function so

that

quote:

> I can select from it. (I understand that you can't do "SELECT * FROM
> SP_MYPROC GROUP BY etc.", whereas you can do "SELECT * FROM UDF_MYFUNC

GROUP

quote:

> BY etc." where SP_MYPROC is a stored procedure and UDF_MYFUNC is a
> user-defined function.) I am suggesting this because I have the impression
> that stored procedures and user-defined functions are pre-compiled with an
> execution plan by SQL server, whereas this is not possible for views.
> Many thanks in advance,
> Richard Cox.
>
>

|||Thanks very much for your explanation, Geoff. Looks like there is nothing
much to be gained in this case then.
Richard.|||Richard
Query Optimyzer does not produce query plan for views. On other hand when
you create clustered index on view it is materialized and store in the same
way as store clusetred index created on the table. I have seen queries that
after adding clustered index have ran more faster.
"Richard Cox" <rpcox@.traqs.com> wrote in message
news:e8IESL$4DHA.1852@.TK2MSFTNGP10.phx.gbl...

quote:

> Thanks very much for your explanation, Geoff. Looks like there is nothing
> much to be gained in this case then.
> Richard.
>

Optimising Query based on Views

I would be grateful for some advice.
I have a query which selects from a view which, in turn, is based on three
other view. I want to optimise the query.
The query is built dynamically so cannot be made into a stored procedure. Is
it worth my while making the main view into a user-defined function so that
I can select from it. (I understand that you can't do "SELECT * FROM
SP_MYPROC GROUP BY etc.", whereas you can do "SELECT * FROM UDF_MYFUNC GROUP
BY etc." where SP_MYPROC is a stored procedure and UDF_MYFUNC is a
user-defined function.) I am suggesting this because I have the impression
that stored procedures and user-defined functions are pre-compiled with an
execution plan by SQL server, whereas this is not possible for views.
Many thanks in advance,
Richard Cox.Yes and no. Views are totally transparent to the optimizer and are only
useful as an abstraction layer and security feature.. The optimizer looks
at the underlying tables rather than the view, EXCEPT for partitioned views
which I will conveniently ignore here.
The big advantage to a stored procedure is query plan reuse. The optimizer
figures out its 'best' plan once and reuses it until it is no longer valid
or it is aged out of cache. If you call the procedure once a day, this
won't help much. On the other hand, even stored procedures have limits.
Temporary tables and dynamic SQL are two of the biggest reasons why a stored
procedure will be recompiled.
From your description, the result set and filter conditions may change from
execution to execution so the advantage of plan reuse just doesn't apply.
You may have to construct a few samples and see if the optimizer does what
you think it should. Use the 'View Estimated Execution Plan' button in
Query Analyzer to see what SQL will do with various combinations of your
query.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
CareerBuilder.com
"Richard Cox" <rpcox@.traqs.com> wrote in message
news:en6D1q54DHA.2736@.TK2MSFTNGP09.phx.gbl...
> I would be grateful for some advice.
> I have a query which selects from a view which, in turn, is based on three
> other view. I want to optimise the query.
> The query is built dynamically so cannot be made into a stored procedure.
Is
> it worth my while making the main view into a user-defined function so
that
> I can select from it. (I understand that you can't do "SELECT * FROM
> SP_MYPROC GROUP BY etc.", whereas you can do "SELECT * FROM UDF_MYFUNC
GROUP
> BY etc." where SP_MYPROC is a stored procedure and UDF_MYFUNC is a
> user-defined function.) I am suggesting this because I have the impression
> that stored procedures and user-defined functions are pre-compiled with an
> execution plan by SQL server, whereas this is not possible for views.
> Many thanks in advance,
> Richard Cox.
>
>|||Thanks very much for your explanation, Geoff. Looks like there is nothing
much to be gained in this case then.
Richard.|||Richard
Query Optimyzer does not produce query plan for views. On other hand when
you create clustered index on view it is materialized and store in the same
way as store clusetred index created on the table. I have seen queries that
after adding clustered index have ran more faster.
"Richard Cox" <rpcox@.traqs.com> wrote in message
news:e8IESL$4DHA.1852@.TK2MSFTNGP10.phx.gbl...
> Thanks very much for your explanation, Geoff. Looks like there is nothing
> much to be gained in this case then.
> Richard.
>

Optimising queries

Hi.

Maybe I'm just being dim, but I'm struggling to get my head around
optimising a query with regard to indexes. If I make a select query, such
as a pseudo-example 'select * from bigtable where foo='bar' and
(barney>rubble and fred<flintoff)', and the table is indexed on 'foo', how
could I make that any better? What indexes could I add, or what could I
change in the query?

I know it looks simple, but so am I.

Cheers

Chris WestonChris Weston (chrisweston[losethislot]@.ntlworld.com) writes:
> Maybe I'm just being dim, but I'm struggling to get my head around
> optimising a query with regard to indexes. If I make a select query, such
> as a pseudo-example 'select * from bigtable where foo='bar' and
> (barney>rubble and fred<flintoff)', and the table is indexed on 'foo', how
> could I make that any better? What indexes could I add, or what could I
> change in the query?
> I know it looks simple, but so am I.

First of all, it matters what index on 'foo' that you have. Is that a
clustered index or a non-clustered index? For this query a clustered
index is is likely to be better, but since you only can have one clustered
index on a table, there may be better choices for other queries.

It's unclear to me what

(barney>rubble and fred<flintoff)

is supposed to mean, but I assume that barney and fred are columns and
'rubble' and 'flintoff' are values.

It's difficult to cover this condition well in a single index. I don't
thinks it much use to include both in the clustered index, but you should
pick one and make it (foo, barney) or (foo, fred).

If you have to use non-clustered indexes is a little different.
(foo, barney, fred) is proabbly more effective than (foo, barney),
because SQL Server does have to access the data pages to check
the condition on fred.

Yet an idea, is to have (foo, barney) and (foo, fred) and see if
SQL Server may use index intersection.

As for changing the query, that's difficult, because I don't know what
it is supposed to mean.

Overall, it's difficult to give generic advice for performance issues,
since there are a lot of "it depends".

--
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|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns978FEFA60586BYazorman@.127.0.0.1...
> Chris Weston (chrisweston[losethislot]@.ntlworld.com) writes:
> > Maybe I'm just being dim, but I'm struggling to get my head around
> > optimising a query with regard to indexes. If I make a select query,
such
> > as a pseudo-example 'select * from bigtable where foo='bar' and
> > (barney>rubble and fred<flintoff)', and the table is indexed on 'foo',
how
> > could I make that any better? What indexes could I add, or what could I
> > change in the query?
> > I know it looks simple, but so am I.
> First of all, it matters what index on 'foo' that you have. Is that a
> clustered index or a non-clustered index? For this query a clustered
> index is is likely to be better, but since you only can have one clustered
> index on a table, there may be better choices for other queries.
> It's unclear to me what
> (barney>rubble and fred<flintoff)
> is supposed to mean, but I assume that barney and fred are columns and
> 'rubble' and 'flintoff' are values.
> It's difficult to cover this condition well in a single index. I don't
> thinks it much use to include both in the clustered index, but you should
> pick one and make it (foo, barney) or (foo, fred).
> If you have to use non-clustered indexes is a little different.
> (foo, barney, fred) is proabbly more effective than (foo, barney),
> because SQL Server does have to access the data pages to check
> the condition on fred.
> Yet an idea, is to have (foo, barney) and (foo, fred) and see if
> SQL Server may use index intersection.
> As for changing the query, that's difficult, because I don't know what
> it is supposed to mean.
> Overall, it's difficult to give generic advice for performance issues,
> since there are a lot of "it depends".

I should have been clearer over the conditions, but you made the correct
assumption. That's useful advice, thank you very much. I can certainly add
more indexing as you suggest, but is there any performance or resource
overhead in having many indexes?

Thanks
Chris Weston|||Chris Weston (chrisweston[losethislot]@.ntlworld.com) writes:
> I should have been clearer over the conditions, but you made the correct
> assumption. That's useful advice, thank you very much. I can certainly
> add more indexing as you suggest, but is there any performance or
> resource overhead in having many indexes?

There is no such thing as free lunch, and an index comes with a cost yes.
The more indexes there are on a table, the longer inserts, updates and
deletes will take. It's difficult to quantify. Adding one more index, rarely
gives dramatic effect on these operations, but eventually there may be a
straw that breaks the camel's back. On the other hand, adding an index
can have drastic impact on a query, usually to the better.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Find things that are pretty unique, or at least define what you want
down pretty closely.

Dates are usually good. Male/Female or Yes/No is really bad.

Don't be shy about using composite indexes, but the order of things in
your composite index can be important.|||oh.
the overhead for indexes comes in teh creation (one time, not too bad),
inserts, and deletes and updates.

Not usually a HUGE issue unless you are doing huge transaction tables.

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;

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

);

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;

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;

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;

USE tempdb;

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

);

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;

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;

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

Optimising a query / stored procedure

I have a Stored Procedure based on a View which is running very slowly, so
we need to rewrite it. (Need to reduce from 10sec to 1sec)
Using Query Analyser we Executed the Stored Procedure and it takes 10 Sec.
However re-Executing with same parameters it only takes 1 sec, it's like the
Query Optimiser has cached or remembered the Execution Plan or something.
(If I use a new set or parameters then it takes 10 secs again)
Unless we can get a consistent result of how long the current SP takes to
run, there's no way of determining whether any changes are beneficial - is
there some way of clearing the "cache" or "un-remembering" the Execution
Plan so that we can get a consistent result to compare with ?
(This is SQL Server 2000 standard edition)Look at
DBCC DROPCLEANBUFFER
DBCC FREEPROCCACHE
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Richard" <richa@.heidmar.co.uk> wrote in message
news:EHNkb.10$to6.7@.newsr2.u-net.net...
> I have a Stored Procedure based on a View which is running very slowly, so
> we need to rewrite it. (Need to reduce from 10sec to 1sec)
> Using Query Analyser we Executed the Stored Procedure and it takes 10 Sec.
> However re-Executing with same parameters it only takes 1 sec, it's like
the
> Query Optimiser has cached or remembered the Execution Plan or something.
> (If I use a new set or parameters then it takes 10 secs again)
> Unless we can get a consistent result of how long the current SP takes to
> run, there's no way of determining whether any changes are beneficial - is
> there some way of clearing the "cache" or "un-remembering" the Execution
> Plan so that we can get a consistent result to compare with ?
> (This is SQL Server 2000 standard edition)
>|||... and CHECKPOINT in the beginning to get rid of dirty pages.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:ei6GoxulDHA.3316@.tk2msftngp13.phx.gbl...
> Look at
> DBCC DROPCLEANBUFFER
> DBCC FREEPROCCACHE
>
> --
> --
> Allan Mitchell (Microsoft SQL Server MVP)
> MCSE,MCDBA
> www.SQLDTS.com
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
>
> "Richard" <richa@.heidmar.co.uk> wrote in message
> news:EHNkb.10$to6.7@.newsr2.u-net.net...
> > I have a Stored Procedure based on a View which is running very slowly, so
> > we need to rewrite it. (Need to reduce from 10sec to 1sec)
> > Using Query Analyser we Executed the Stored Procedure and it takes 10 Sec.
> > However re-Executing with same parameters it only takes 1 sec, it's like
> the
> > Query Optimiser has cached or remembered the Execution Plan or something.
> > (If I use a new set or parameters then it takes 10 secs again)
> > Unless we can get a consistent result of how long the current SP takes to
> > run, there's no way of determining whether any changes are beneficial - is
> > there some way of clearing the "cache" or "un-remembering" the Execution
> > Plan so that we can get a consistent result to compare with ?
> >
> > (This is SQL Server 2000 standard edition)
> >
> >
>

Monday, March 19, 2012

Optimising Select statements which has a LIKE where clause.

Optimising Query based on Views

Optimising Query based on Views

Optimising queries

optimising a table with lots of boolean fields

Optimising a query / stored procedure

OpenXML Identity Insert

Blog Archive

About Me