Performance Tuning UPDATE Statement

Below is a simple UPDATE that I have to perform on a table that has
about 2.5 million rows (about 4 million in production) This query
runs for an enourmous amount of time (over 1 hour). Both the
ChangerRoleID and the ChangerID are indexed (not unique). Is there
any way to performance tune this?

Controlling the physical drive of the log file isn't possible at our
client sites (we don't have control) and the recovery model needs to
be set to "Full".

UPDATE CLIENTSHISTORY SET ChangerRoleID = ChangerID WHERE
ChangerRoleID IS NULL

Any Help would be greatly appreciated!On 4 Aug 2004 08:27:50 -0700, MAS wrote:

>Below is a simple UPDATE that I have to perform on a table that has
>about 2.5 million rows (about 4 million in production) This query
>runs for an enourmous amount of time (over 1 hour). Both the
>ChangerRoleID and the ChangerID are indexed (not unique). Is there
>any way to performance tune this?
>Controlling the physical drive of the log file isn't possible at our
>client sites (we don't have control) and the recovery model needs to
>be set to "Full".
>UPDATE CLIENTSHISTORY SET ChangerRoleID = ChangerID WHERE
>ChangerRoleID IS NULL
>Any Help would be greatly appreciated!

Hi MAS,

If you remove the non-unique index on ChangerRoleID before doing the
update and recreate it afterwards, you'll probably save some time. The
index could have been useful if only a few of all rows match the IS NULL
condition, but with over aan hour execution time, I think there are so
many matches that a full table scan will be quicker. Removing the index
before doing the update saves SQL Server the extra work of constantly
having to update the index to keep it in sync with the data. Of course,
this might affect other queries that execute during the update and would
have benefited from this index. The index on ChangerID will neither be
used nor cause extra work for this update.

Check if there's a trigger that gets fired by the update. If you can
safely disable that trigger during the update process, do so. Same for
constraints: are there any CHECK or REFERENCES (foreign key) constraints
defined for ChangerRoleID? If so, disable constraint checking (again, only
if it is safe, i.e. you have to be sure that this update won't cause
violation of the constraint *and* that no other person accessing the
database during the time constraint checking is disabled will be able to
cause violations of the constraint).

You state that the recovery model needs to be full; from that I conclude
that you can't lock other users out of the database during the update. Can
you at least take measures to prevent other users from using (updating,
but preferably reading as well) the CLIENTSHISTORY table?

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||[posted and mailed, please reply in news]

MAS (mas32677@.hotmail.com) writes:
> Below is a simple UPDATE that I have to perform on a table that has
> about 2.5 million rows (about 4 million in production) This query
> runs for an enourmous amount of time (over 1 hour). Both the
> ChangerRoleID and the ChangerID are indexed (not unique). Is there
> any way to performance tune this?
> Controlling the physical drive of the log file isn't possible at our
> client sites (we don't have control) and the recovery model needs to
> be set to "Full".
> UPDATE CLIENTSHISTORY SET ChangerRoleID = ChangerID WHERE
> ChangerRoleID IS NULL
> Any Help would be greatly appreciated!

To add to what Hugo said, if that index on ChangerRoleID is clustered,
and many rows have a NULL value, then you are in for a problem.

It may help to do it batches:

DECLARE @.batch_size int, @.rowc int
SELECT @.batch_size = 50000
SELECT @.rowc = @.batch_size
SET ROWCOUNT @.batch_size
WHILE @.rowc = @.batch_size
BEGIN
UPDATE CLIENTSHISTORY SET ChangerRoleID = ChangerID
WHERE ChangerRoleID IS NULL
AND ChangerID IS NOT NULL
SELECT @.rowc = @.@.rowcount
END
SET ROWCOUNT 0

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Performance Tuning SQL query in Trigger

i am using sql server 2000. I have written update trigger on CORP_CAGE table to log the details in

CORP_CAGE_LOG_HIST table,if any changes in EMP_SEQ_NO column.

please find the structure of CORP_CAGE table:

1.CORP_CAGE_SEQ_NO
2.RECEIVED_DATE
3.EMP_SEQ_NO

CORP_CAGE table is having 50,000 records. the trigger "Check_Update" is fired when i am executing the following

query from application which updates 10,000 records.

UPDATE CORP_CAGE SET EMP_SEQ_NO=NULL WHERE EMP_SEQ_NO=111

please find below the trigger,in that, trigger can easily find whether any UPDATE done in EMP_SEQ_NO column by using

UPDATE FUNCTION.
But,when it come to insert part, it takes more time(nearly 1 hour or sometimes it will hang.).For minimum

records,this trigger is working fine.

Create trigger Check_Update ON dbo.CORP_CAGE FOR UPDATE AS
BEGIN
IF UPDATE(EMP_SEQ_NO)
BEGIN
INSERT CORP_CAGE_LOG_HIST
(
CAGE_LOG_SEQ_NUM,
BEFORE_VALUE,
AFTER_VALUE,
ENTRY_USER,
FIELD_UPDATED
)
SELECT
i.CAGE_LOG_SEQ_NUM,
d.RECEIVED_DATE,
i.RECEIVED_DATE,
i.UPDATE_USER,
"EMP_SEQ_NO"
FROM
inserted i,
deleted d
WHERE
i.CAGE_LOG_SEQ_NUM = d.CAGE_LOG_SEQ_NUM
END

END

please help me on this for performance tuning the below query.

I don't have the schema of your table, which in this case is critical. However, if this statement:

Code Snippet

UPDATE CORP_CAGE SET EMP_SEQ_NO=NULL WHERE EMP_SEQ_NO=111

is updating 10,000 records then your join is going to cause an update that is the cross product of 10,000 x 10,000 or 100,000,000 logical records. This cannot be right. Look at your trigger WHERE condition:

Code Snippet

WHERE
i.CAGE_LOG_SEQ_NUM = d.CAGE_LOG_SEQ_NUM

since you are updating ONLY for SEQ_NO = 111 and you are joining the INSERTED pseudo table -- with 10,000 records -- with the DELETE pseudo table -- also with 10,000 records -- and since all records of the DELETED pseudo and all records of the DELETED pseudo have the same SEQ_NO -- specifically 111 you end up with the cross product. You need to linclude the KEY information as part of the join condition.

sql

Performance Tuning for Row-by-Row Update Statement

hi

For an unavoidable reason, I have to use row-by-row processing
(update) on a temporary table to update a history table every day.
I have around 60,000 records in temporary table and about 2 million in
the history table.

Could any one please suggest different methods to imporve the runtime
of the query?

Would highly appreciate!Is the row-by-row processing done in a cursor? Must you update exactly one
row at a time (if so, why?) or would it be acceptable to update 2,3 or 50
rows at a time?

You can use SET ROWCOUNT and a loop to fine-tune the batch size of rows to
be updated. Bigger batches should improve performance over updating single
rows.

SET ROWCOUNT 50

WHILE 1=1
BEGIN

UPDATE SomeTable
SET ...
WHERE /* row not already updated */

IF @.@.ROWCOUNT=0
BREAK

END

SET ROWCOUNT 0

--
David Portas
SQL Server MVP
--|||Is the row-by-row processing done in a cursor? Must you update exactly one
row at a time (if so, why?) or would it be acceptable to update 2,3 or 50
rows at a time?

You can use SET ROWCOUNT and a loop to fine-tune the batch size of rows to
be updated. Bigger batches should improve performance over updating single
rows.

SET ROWCOUNT 50

WHILE 1=1
BEGIN

UPDATE SomeTable
SET ...
WHERE /* row not already updated */

IF @.@.ROWCOUNT=0
BREAK

END

SET ROWCOUNT 0

--
David Portas
SQL Server MVP
--|||"Muzamil" <muzamil@.hotmail.com> wrote in message
news:5a998f78.0405211023.24b40513@.posting.google.c om...
> hi
> For an unavoidable reason, I have to use row-by-row processing
> (update) on a temporary table to update a history table every day.
> I have around 60,000 records in temporary table and about 2 million in
> the history table.

Not much you can do if you absolutely HAVE to do row-by-row updating.

You might want to post DDL, etc. so others can take a crack at it. I've
seen many times someone will say, "I have to use a cursor", "I have to
update one row at a time" and then someone posts a much better/faster
solution.

Also, how are you handling transactions? Explicitly or implicitely? If
you're doing them implicitely, are you wrapping each update in its own, or
can up batch say 20 updates?

Finally, where's your log files? Separate physical drives?

> Could any one please suggest different methods to imporve the runtime
> of the query?
> Would highly appreciate!|||Hi
Thanks for your reply.

The row-by-row update is mandatory becuase the leagacy system is
sending us the information such as "Add", "Modify" or "delete" and
this information HAS to be processed in the same order otherwise we'll
get the erroneous data.
I know it's a dumb way of doing things but this is what our and their
IT department has chosen to be correct way of action after several
meetings. Hence the batch idea will not work here.

I am not using Cursors, instead I am using the loop based on the
primary key.

The log files are on different drives.

I've also tried using "WITH (ROWLOCK)" in the update statement but
it's not helping much.

Can you please still throw in some idea? Would be great help!

Thanks

"Greg D. Moore \(Strider\)" <mooregr_deleteth1s@.greenms.com> wrote in message news:<tOxrc.234090$M3.65389@.twister.nyroc.rr.com>...
> "Muzamil" <muzamil@.hotmail.com> wrote in message
> news:5a998f78.0405211023.24b40513@.posting.google.c om...
> > hi
> > For an unavoidable reason, I have to use row-by-row processing
> > (update) on a temporary table to update a history table every day.
> > I have around 60,000 records in temporary table and about 2 million in
> > the history table.
> Not much you can do if you absolutely HAVE to do row-by-row updating.
> You might want to post DDL, etc. so others can take a crack at it. I've
> seen many times someone will say, "I have to use a cursor", "I have to
> update one row at a time" and then someone posts a much better/faster
> solution.
> Also, how are you handling transactions? Explicitly or implicitely? If
> you're doing them implicitely, are you wrapping each update in its own, or
> can up batch say 20 updates?
> Finally, where's your log files? Separate physical drives?
>
> > Could any one please suggest different methods to imporve the runtime
> > of the query?
> > Would highly appreciate!|||Muzamil (muzamil@.hotmail.com) writes:
> The row-by-row update is mandatory becuase the leagacy system is
> sending us the information such as "Add", "Modify" or "delete" and
> this information HAS to be processed in the same order otherwise we'll
> get the erroneous data.

Ouch. Life is cruel, sometimes.

I wonder what possibilities there could be to find parallel streams,
that is updates that could be performed independently. Maybe you
can modify 10 rows at a time then. But it does not sound like a very
easy thing to do.

Without knowing the details of the system, it is difficult to give
much advice. But any sort of pre-aggregation you can do, is probably
going to pay back.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Details of the system:
The leagcy system sends us records flagged with "Add", "modify" or
"delete".
The purpose of these flags is self-explnatory. But the fun began when
we noticed that within same file , legacy system sends us "Add" and
then "Modify". Thus, we were left with no other option except to do
row-by-row processing.
We came up with the following logic:

a)If records StatusFlag is A' and records key does not exist in
DataWareHouse's Table, then the record is inserted into
DataWareHouse's Table.

b)If records StatusFlag is A', but records key exists in
DataWareHouse's Table, then the record is marked as invalid and will
be inserted into InvalidTable..

c)If records StatusFlag is M' and records key exists in
DataWareHouse's Table and record is active, then the corresponding
record in DataWareHouse's Table will be updated.

d)If records StatusFlag is M' and records key exists in
DataWareHouse's Table but record is inactive, then the record is
marked as invalid and will be inserted into InvalidTable.

e)If records StatusFlag is M' and records key does not exist in
DataWareHouse's Table, then the record is marked as invalid and will
be inserted into InvalidTable.

f)If records StatusFlag is D' and records key exists in
DataWareHouse's Table and record is active, then the corresponding
record in DataWareHouse's Table will be updated as inactive.

g)If records StatusFlag is D' and records key exists in
DataWareHouse's Table but record is inactive, then the record is
marked as invalid and will be inserted into InvalidTable.

h)If records StatusFlag is D' and records key does not exist in
DataWareHouse's Table, then the record is marked as invalid and will
be inserted into InvalidTable.

This logic takes care of ALL the anomalies we were facing before but
at the cost of long processing time.

I await your comments.

Thanks

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns94F53BF51111Yazorman@.127.0.0.1>...
> Muzamil (muzamil@.hotmail.com) writes:
> > The row-by-row update is mandatory becuase the leagacy system is
> > sending us the information such as "Add", "Modify" or "delete" and
> > this information HAS to be processed in the same order otherwise we'll
> > get the erroneous data.
> Ouch. Life is cruel, sometimes.
> I wonder what possibilities there could be to find parallel streams,
> that is updates that could be performed independently. Maybe you
> can modify 10 rows at a time then. But it does not sound like a very
> easy thing to do.
> Without knowing the details of the system, it is difficult to give
> much advice. But any sort of pre-aggregation you can do, is probably
> going to pay back.|||Muzamil (muzamil@.hotmail.com) writes:
> Details of the system:
> The leagcy system sends us records flagged with "Add", "modify" or
> "delete".
> The purpose of these flags is self-explnatory. But the fun began when
> we noticed that within same file , legacy system sends us "Add" and
> then "Modify". Thus, we were left with no other option except to do
> row-by-row processing.
> We came up with the following logic:

Hm, you might be missing a few cases. What if you get an Add, and record
exists in DW, but is marked inactive? With your current logic, the
input record moved to the Invalid table.

And could that feediug system be as weird as to send Add, Modify, Delete,
and Add again? Well, for a robust solution this is what we should assume.

It's a tricky problem, and I was about to defer the problem, when I
recalled a solution that colleague did for one of our stored procedures.
The secret word for tonight is bucketing! Assuming that there are
only a couple of input records for each key value, this should be
an excellent solution. You create buckets, so that each bucket has
at most one row per key value. Here is an example on how to do it:

UPDATE inputtbl
SET bucket = (SELECT count(*)
FROM inputtbl b
WHERE a.keyval = b.keyval
AND a.rownumber < b.rownumber) + 1
FROM inputtbl a

input.keyval is the keys for the records in the DW table. Rownumber
is a column which as describes the processing order. I assume that
you have such a column.

So now you can iterate over the buckets, and for each bucket, you can do
set- based processing. You still have to iterate, but instead over 60000
rows, only over a couple of buckets.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I think I was not articulate enough to convey the logic properly.
Anyways, thanks to everyone for your help.
By using the ROWLOCK and proper indexes, I was ale to reduce the time considerably.

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns94F6821D6ABYazorman@.127.0.0.1>...
> Muzamil (muzamil@.hotmail.com) writes:
> > Details of the system:
> > The leagcy system sends us records flagged with "Add", "modify" or
> > "delete".
> > The purpose of these flags is self-explnatory. But the fun began when
> > we noticed that within same file , legacy system sends us "Add" and
> > then "Modify". Thus, we were left with no other option except to do
> > row-by-row processing.
> > We came up with the following logic:
> Hm, you might be missing a few cases. What if you get an Add, and record
> exists in DW, but is marked inactive? With your current logic, the
> input record moved to the Invalid table.
> And could that feediug system be as weird as to send Add, Modify, Delete,
> and Add again? Well, for a robust solution this is what we should assume.
> It's a tricky problem, and I was about to defer the problem, when I
> recalled a solution that colleague did for one of our stored procedures.
> The secret word for tonight is bucketing! Assuming that there are
> only a couple of input records for each key value, this should be
> an excellent solution. You create buckets, so that each bucket has
> at most one row per key value. Here is an example on how to do it:
> UPDATE inputtbl
> SET bucket = (SELECT count(*)
> FROM inputtbl b
> WHERE a.keyval = b.keyval
> AND a.rownumber < b.rownumber) + 1
> FROM inputtbl a
> input.keyval is the keys for the records in the DW table. Rownumber
> is a column which as describes the processing order. I assume that
> you have such a column.
> So now you can iterate over the buckets, and for each bucket, you can do
> set- based processing. You still have to iterate, but instead over 60000
> rows, only over a couple of buckets.|||Muzamil (muzamil@.hotmail.com) writes:
> I think I was not articulate enough to convey the logic properly.
> Anyways, thanks to everyone for your help. By using the ROWLOCK and
> proper indexes, I was ale to reduce the time considerably.

Good indexes is always useful, and of course for iterative processing
it is even more imperative, since the cost a less-than-optimal plan
is multiplied.

I'm just curious, would my bucketing idea be applicable to your problem?
It should give you even more speed, but if you have good-ebough now, there
is of course no reason to spend more time on it.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Performance tuning and measure on MSSQL 2000

Hi

I am trying to design an IO subsystem for my SQL Server and for that I
need to try and predict IO activity on each table in my MSSQL
Database. My idea is to move the hottest tables into special disk
subsystem away from the less hotter tables. So far I have gathered
that we have three tables more hot than the others but I have no
feeling on ratio on how hot each is and how much activity is on the
less hotter tables. I need to predict how many disks I should assign
to each subsystem and so far...
I haven't found a reasonable way to do this.

The only way I found to see read/writes and physical read/writes is on
filelevel. but I've also managed to do a trace in sqlprofiler to get
the logical read and writes per query but since my queries are often
joins I have no way of spliting that IO between the tables included in
the join and no idea on which hit the buffer pool and which didn'nt.
Is there maybe a counter or some way that I have not found?

Any input would be greatly appriciated.

best regards & thanks
Arni Snorriarnie@.gormur.com (Arni Snorri Eggertsson) wrote in message news:<c8d15bfa.0404280125.6f1dadcf@.posting.google.com>...
> Hi
> I am trying to design an IO subsystem for my SQL Server and for that I
> need to try and predict IO activity on each table in my MSSQL
> Database. My idea is to move the hottest tables into special disk
> subsystem away from the less hotter tables. So far I have gathered
> that we have three tables more hot than the others but I have no
> feeling on ratio on how hot each is and how much activity is on the
> less hotter tables. I need to predict how many disks I should assign
> to each subsystem and so far...
> I haven't found a reasonable way to do this.
> The only way I found to see read/writes and physical read/writes is on
> filelevel. but I've also managed to do a trace in sqlprofiler to get
> the logical read and writes per query but since my queries are often
> joins I have no way of spliting that IO between the tables included in
> the join and no idea on which hit the buffer pool and which didn'nt.
> Is there maybe a counter or some way that I have not found?
> Any input would be greatly appriciated.
> best regards & thanks
> Arni Snorri

I'm not sure if it's possible to do exactly what you want - MSSQL will
probably cache a lot of the data from the 'hot' tables anyway, so the
issue is not so much the physical disk access as how much RAM you
have, and how well MSSQL uses the cache. There are a lot of
performance monitor counters for buffer and cache management you can
use to look at this.

As for the disks, I would start by identifying how much space is
required on disk, then try to use lots of smaller disks instead of
fewer bigger ones for the 'hot' filegroups. Placing the transaction
logs on separate disks would also help, of course.

Simon|||"Arni Snorri Eggertsson" <arnie@.gormur.com> wrote in message
news:c8d15bfa.0404280125.6f1dadcf@.posting.google.c om...
> Hi
> I am trying to design an IO subsystem for my SQL Server and for that I
> need to try and predict IO activity on each table in my MSSQL
> Database. My idea is to move the hottest tables into special disk
> subsystem away from the less hotter tables. So far I have gathered
> that we have three tables more hot than the others but I have no
> feeling on ratio on how hot each is and how much activity is on the
> less hotter tables. I need to predict how many disks I should assign
> to each subsystem and so far...
> I haven't found a reasonable way to do this.

If you don't have it, get the Microsoft Press book on SQL Server Performance
tuning. Lots of good help here.

> The only way I found to see read/writes and physical read/writes is on
> filelevel. but I've also managed to do a trace in sqlprofiler to get
> the logical read and writes per query but since my queries are often
> joins I have no way of spliting that IO between the tables included in
> the join and no idea on which hit the buffer pool and which didn'nt.
> Is there maybe a counter or some way that I have not found?
> Any input would be greatly appriciated.
> best regards & thanks
> Arni Snorri

Performance Tuning

I have a query that i'm trying to performance tune a little better.
i'm stuck on one thing. i have a table that i join on mulitple times
that selects the max date for a particular status per id.
the table looks as follows:
CREATE TABLE [dbo].[TABLE_STATUS] (
[TableStatusID] [int] NOT NULL ,
[TableID] [int] NOT NULL ,
[StatusTypeID] [int] NOT NULL ,
[StatusDate] [datetime] NOT NULL ,
[CreateDate] [datetime] NOT NULL ,
[StageTypeID] [int] NULL
) ON [PRIMARY]
GO
The query i'm using looks something like this:
SELECT field1, field2, field3
(SELECT TOP 1 StatusDate
FROM dbo.TABLE_STATUS LS
WHERE LS.TableID= SR.TableIDAND StatusTypeID = 'xyx'
ORDER BY StatusDate DESC) AS someDate,
(SELECT TOP 1 StatusDate
FROM dbo.TABLE_STATUS LS
WHERE LS.TableID= SR.TableIDAND StatusTypeID = 'xxy'
ORDER BY StatusDate DESC) AS someDate,
(SELECT TOP 1 StatusDate
FROM dbo.TABLE_STATUS LS
WHERE LS.TableID= SR.TableIDAND StatusTypeID = 'yxx'
ORDER BY StatusDate DESC) AS someDate
FROM dbo.someTable SR
WHERE etc.
I was wondering if there is a better to way to select the max date for
each loan by date desc. some queries use up too 25 different
max(dates) per select statement. There just has to be a better way
performance wise.
Thanks ahead of time.On 17 May 2005 11:01:01 -0700, GlennThomas5 wrote:

>I have a query that i'm trying to performance tune a little better.
>i'm stuck on one thing. i have a table that i join on mulitple times
>that selects the max date for a particular status per id.
>the table looks as follows:
>CREATE TABLE [dbo].[TABLE_STATUS] (
> [TableStatusID] [int] NOT NULL ,
> [TableID] [int] NOT NULL ,
> [StatusTypeID] [int] NOT NULL ,
> [StatusDate] [datetime] NOT NULL ,
> [CreateDate] [datetime] NOT NULL ,
> [StageTypeID] [int] NULL
> ) ON [PRIMARY]
>GO
>The query i'm using looks something like this:
>SELECT field1, field2, field3
> (SELECT TOP 1 StatusDate
> FROM dbo.TABLE_STATUS LS
> WHERE LS.TableID= SR.TableIDAND StatusTypeID = 'xyx'
> ORDER BY StatusDate DESC) AS someDate,
> (SELECT TOP 1 StatusDate
> FROM dbo.TABLE_STATUS LS
> WHERE LS.TableID= SR.TableIDAND StatusTypeID = 'xxy'
> ORDER BY StatusDate DESC) AS someDate,
> (SELECT TOP 1 StatusDate
> FROM dbo.TABLE_STATUS LS
> WHERE LS.TableID= SR.TableIDAND StatusTypeID = 'yxx'
> ORDER BY StatusDate DESC) AS someDate
> FROM dbo.someTable SR
> WHERE etc.
>I was wondering if there is a better to way to select the max date for
>each loan by date desc. some queries use up too 25 different
>max(dates) per select statement. There just has to be a better way
>performance wise.
Hi Glenn,
Try if this works for you:
SELECT field1, field2, field3,
MAX(CASE WHEN LS.StatusTypeID = 'xyx' THEN LS.StatusDate END),
MAX(CASE WHEN LS.StatusTypeID = 'xxy' THEN LS.StatusDate END),
MAX(CASE WHEN LS.StatusTypeID = 'yxx' THEN LS.StatusDate END)
FROM dbo.someTable SR
JOIN dbo.TABLE_STATUS LS
ON LS.TableID= SR.TableID
WHERE ...
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks i'm going to check it out right now. =)|||well i check it out and that seems to be pulling the 1 max(date) across
all the typeid's. i need the max(date) for each typeid.|||tweaking a few more things. i think i might have it.|||this worked perfect. thanks again.

Performance Tuning

I have a query that i'm trying to performance tune a little better.
i'm stuck on one thing. i have a table that i join on mulitple times
that selects the max date for a particular status per id.
the table looks as follows:
CREATE TABLE [dbo].[TABLE_STATUS] (
[TableStatusID] [int] NOT NULL ,
[TableID] [int] NOT NULL ,
[StatusTypeID] [int] NOT NULL ,
[StatusDate] [datetime] NOT NULL ,
[CreateDate] [datetime] NOT NULL ,
[StageTypeID] [int] NULL
) ON [PRIMARY]
GO
The query i'm using looks something like this:
SELECT field1, field2, field3
(SELECT TOP 1 StatusDate
FROM dbo.TABLE_STATUS LS
WHERE LS.TableID= SR.TableIDAND StatusTypeID = 'xyx'
ORDER BY StatusDate DESC) AS someDate,
(SELECT TOP 1 StatusDate
FROM dbo.TABLE_STATUS LS
WHERE LS.TableID= SR.TableIDAND StatusTypeID = 'xxy'
ORDER BY StatusDate DESC) AS someDate,
(SELECT TOP 1 StatusDate
FROM dbo.TABLE_STATUS LS
WHERE LS.TableID= SR.TableIDAND StatusTypeID = 'yxx'
ORDER BY StatusDate DESC) AS someDate
FROM dbo.someTable SR
WHERE etc.
I was wondering if there is a better to way to select the max date for
each loan by date desc. some queries use up too 25 different
max(dates) per select statement. There just has to be a better way
performance wise.
Thanks ahead of time.
On 17 May 2005 11:01:01 -0700, GlennThomas5 wrote:

>I have a query that i'm trying to performance tune a little better.
>i'm stuck on one thing. i have a table that i join on mulitple times
>that selects the max date for a particular status per id.
>the table looks as follows:
>CREATE TABLE [dbo].[TABLE_STATUS] (
>[TableStatusID] [int] NOT NULL ,
>[TableID] [int] NOT NULL ,
>[StatusTypeID] [int] NOT NULL ,
>[StatusDate] [datetime] NOT NULL ,
>[CreateDate] [datetime] NOT NULL ,
>[StageTypeID] [int] NULL
>) ON [PRIMARY]
>GO
>The query i'm using looks something like this:
>SELECT field1, field2, field3
> (SELECT TOP 1 StatusDate
> FROM dbo.TABLE_STATUS LS
> WHERE LS.TableID= SR.TableIDAND StatusTypeID = 'xyx'
> ORDER BY StatusDate DESC) AS someDate,
> (SELECT TOP 1 StatusDate
> FROM dbo.TABLE_STATUS LS
> WHERE LS.TableID= SR.TableIDAND StatusTypeID = 'xxy'
> ORDER BY StatusDate DESC) AS someDate,
> (SELECT TOP 1 StatusDate
> FROM dbo.TABLE_STATUS LS
> WHERE LS.TableID= SR.TableIDAND StatusTypeID = 'yxx'
> ORDER BY StatusDate DESC) AS someDate
> FROM dbo.someTable SR
> WHERE etc.
>I was wondering if there is a better to way to select the max date for
>each loan by date desc. some queries use up too 25 different
>max(dates) per select statement. There just has to be a better way
>performance wise.
Hi Glenn,
Try if this works for you:
SELECT field1, field2, field3,
MAX(CASE WHEN LS.StatusTypeID = 'xyx' THEN LS.StatusDate END),
MAX(CASE WHEN LS.StatusTypeID = 'xxy' THEN LS.StatusDate END),
MAX(CASE WHEN LS.StatusTypeID = 'yxx' THEN LS.StatusDate END)
FROM dbo.someTable SR
JOIN dbo.TABLE_STATUS LS
ON LS.TableID= SR.TableID
WHERE ...
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks i'm going to check it out right now. =)
|||well i check it out and that seems to be pulling the 1 max(date) across
all the typeid's. i need the max(date) for each typeid.
|||tweaking a few more things. i think i might have it.
|||this worked perfect. thanks again.

Performance Tuning

I have a query that i'm trying to performance tune a little better.
i'm stuck on one thing. i have a table that i join on mulitple times
that selects the max date for a particular status per id.
the table looks as follows:
CREATE TABLE [dbo].[TABLE_STATUS] (
[TableStatusID] [int] NOT NULL ,
[TableID] [int] NOT NULL ,
[StatusTypeID] [int] NOT NULL ,
[StatusDate] [datetime] NOT NULL ,
[CreateDate] [datetime] NOT NULL ,
[StageTypeID] [int] NULL
) ON [PRIMARY]
GO
The query i'm using looks something like this:
SELECT field1, field2, field3
(SELECT TOP 1 StatusDate
FROM dbo.TABLE_STATUS LS
WHERE LS.TableID= SR.TableIDAND StatusTypeID = 'xyx'
ORDER BY StatusDate DESC) AS someDate,
(SELECT TOP 1 StatusDate
FROM dbo.TABLE_STATUS LS
WHERE LS.TableID= SR.TableIDAND StatusTypeID = 'xxy'
ORDER BY StatusDate DESC) AS someDate,
(SELECT TOP 1 StatusDate
FROM dbo.TABLE_STATUS LS
WHERE LS.TableID= SR.TableIDAND StatusTypeID = 'yxx'
ORDER BY StatusDate DESC) AS someDate
FROM dbo.someTable SR
WHERE etc.
I was wondering if there is a better to way to select the max date for
each loan by date desc. some queries use up too 25 different
max(dates) per select statement. There just has to be a better way
performance wise.
Thanks ahead of time.On 17 May 2005 11:01:01 -0700, GlennThomas5 wrote:
>I have a query that i'm trying to performance tune a little better.
>i'm stuck on one thing. i have a table that i join on mulitple times
>that selects the max date for a particular status per id.
>the table looks as follows:
>CREATE TABLE [dbo].[TABLE_STATUS] (
> [TableStatusID] [int] NOT NULL ,
> [TableID] [int] NOT NULL ,
> [StatusTypeID] [int] NOT NULL ,
> [StatusDate] [datetime] NOT NULL ,
> [CreateDate] [datetime] NOT NULL ,
> [StageTypeID] [int] NULL
>) ON [PRIMARY]
>GO
>The query i'm using looks something like this:
>SELECT field1, field2, field3
> (SELECT TOP 1 StatusDate
> FROM dbo.TABLE_STATUS LS
> WHERE LS.TableID= SR.TableIDAND StatusTypeID = 'xyx'
> ORDER BY StatusDate DESC) AS someDate,
> (SELECT TOP 1 StatusDate
> FROM dbo.TABLE_STATUS LS
> WHERE LS.TableID= SR.TableIDAND StatusTypeID = 'xxy'
> ORDER BY StatusDate DESC) AS someDate,
> (SELECT TOP 1 StatusDate
> FROM dbo.TABLE_STATUS LS
> WHERE LS.TableID= SR.TableIDAND StatusTypeID = 'yxx'
> ORDER BY StatusDate DESC) AS someDate
> FROM dbo.someTable SR
> WHERE etc.
>I was wondering if there is a better to way to select the max date for
>each loan by date desc. some queries use up too 25 different
>max(dates) per select statement. There just has to be a better way
>performance wise.
Hi Glenn,
Try if this works for you:
SELECT field1, field2, field3,
MAX(CASE WHEN LS.StatusTypeID = 'xyx' THEN LS.StatusDate END),
MAX(CASE WHEN LS.StatusTypeID = 'xxy' THEN LS.StatusDate END),
MAX(CASE WHEN LS.StatusTypeID = 'yxx' THEN LS.StatusDate END)
FROM dbo.someTable SR
JOIN dbo.TABLE_STATUS LS
ON LS.TableID= SR.TableID
WHERE ...
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks i'm going to check it out right now. =)|||well i check it out and that seems to be pulling the 1 max(date) across
all the typeid's. i need the max(date) for each typeid.|||tweaking a few more things. i think i might have it.|||this worked perfect. thanks again.sql

Performance table vs databases

What is the best thing to do to get the best performance ? Multiple tables
in 1 database or multiple databases containing less tables ?typically, database contain numerous tables.
not sure if your question is clear.....
Greg Jackson
PDX, Oregon|||Hi,
Keeping tables in mutiple database or viceversa will not give you
performance improvements.
Tips for Performance.
1. If you have mutiple disk controllers, Create file groups and place tge
tables and indexes in seperate file group. this will reduce I/O
2. Allot more physicval memory for SQL server
3. Allocate dedicated server for SQL server
4. Design the database in proper and structure manner
5. Create the necessory indexes
6. Optimize your SQL's (Select statements)
Make use of Query optimizer, Performance monitor, Profiler and Index tuning
wizard to tune your application and database.
Thanks
Hari
MCDBA
"Cris" <anonymous@.discussions.microsoft.com> wrote in message
news:D3F1AA37-12C0-4F1F-AEA2-9D4FB10AAB90@.microsoft.com...
> What is the best thing to do to get the best performance ? Multiple
tables in 1 database or multiple databases containing less tables ?|||I don't think performance is the question. There are several techniques to
handle large databases ... but you ask your self if you logically need/shoul
d create two+ databases. You wouldn't typically combine your Reporting with
Orders, right ?
What's the motivation behind your question ?

Performance regarding transposing fact data

Hello

I have an Analysis Services performance question:

Scenario:

My Fact table has a column that indicates some value-ID and only one value column. Out of this fact table I'd like to create a cube with two measures, one for each value-ID. So I could either write a query like

Code Snippet

SELECT key1, key2,

sum(case valueID when 'A' then value else null end) as measureA,

sum(case valueID when 'B' then value else null end) as measureB

FROM myFacts

GROUP by key1, key2

Then I'd use this table or view as fact table.

Or I could include the case syntax directly in the measure definitions inside Analysis Services and use the original table.

Does anybody know what's better concerning performance

-regarding cube processing?

-regarding querying the cube?

Are there other things that make one solution the better one?

Hi,

If you use your select statement to load the data into the cube.

-Processing will be slower as it has to execute the case statement

-Querying will be faster

If you read in the values with out the case statement

-Processing will be quicker as it doesn't have to execute the case statement

-Querying will be slower, you will have to create to MDX calculated measures that do the same as you are showing.

If you use your group by select statement, the granularity of your fact is also going to change - is that really what you want?

I would just create two MDX calculated measures, does depend on the size of data I suppose.

CREATE MEMBER CURRENTCUBE.[measures].[measureA] as

([Measures].[Value],[Dimension].[Dimension Key].&)

,non_empty_behavior = [Measures].[Value],VISIBLE = 1;

Might look something like above - ish

Hope that helps,

Matt

|||

Hi Matt

Thank you for your reply.

(First I apologize for not having added that I'm using Analysis Services 2000 not 2005.)

Generally I don't want to create a calculated member for each measure becaus calculated members are only calculated during run time but I want to have the measures correct after the cube was processed.

In Analysis Services 2000 I don't have the ability to use a select statement as a source for a fact table. Instead a table or view must exist in the underlying datasource. So there I have the possibility to either create a table or view with the select statement above or I enter the case expression inside the measure definition. So somewhere the case will be executed because having a dimension like "valueID" is not what I want or, wait, ...maybe it could be also a possible solution to have just one measure in the cube and have a valueID dimension... But this leads to more difficult queries and some inconveniances for cube users.

Regards

Chris

|||

Hi,

My 2000 skills are a little rusty and weren't perhaps that great in the first place

I would probably create a view, it would allow you to compare A against B or even create a total of A and B if you need to.

Sorry I can't be much help, perhaps someone else will help you.

Cheers

Matt

Performance recommendation

Please give me some advice. In my application I calculate a list of identifiers (Guids) that are primary keys in my table and I have to retrieve those rows from the database. So my first approach is like

Code Snippet

SELECT id, c2 FROM t1 WHERE id IN (@.id1, @.id2, @.id3,....)

where @.idn are the calculated identifies as parameters. This approach does not scale well since there is a limit of parameters that can be used. So one possibility might be to use several SELECT statements, each with the maximum number of parameters. I can't believe that this is a good solution. A temporary table may be a better solution - I don't know. Are there any better ways to retrieve performantly - any recommandations?

Thanks a lot

Hans-Peter

Code Snippet

SELECT id, c2
FROM
t1 a
Join
(Select @.Id1 Index#
union all
Select @.Id2
union all
select @.Id3
...
...
union all
select @.Idn) as b
On
a.Id = b.Index#

|||

First, GUIDs as primary keys is not usually a good idea. See these articles for more information.

GUID -Identity and Primary Keys
http://sqlteam.com/item.asp?ItemID=2599

GUID -Is not Always GOOD
http://bloggingabout.net/blogs/wellink/archive/2004/03/15/598.aspx

GUID -The Cost of GUIDs as Primary Keys
http://www.informit.com/articles/article.asp?p=25862&rl=1

GUID -Uniqueidentifier vs. IDENTITY
http://sqlteam.com/item.asp?ItemID=283

Secondly, using a table variable could be useful.

|||

Arnie is correct that using GUID's for a Primary Key is not a good idea from a performance and maintenance point of view.

It is also a bad idea to use big IN clauses in the WHERE clause of a SELECT statement, since that will give you a large ad-hoc plan with a use count of 1 that will bloat your procedure cache. One work-around to avoid this is to add OPTION (RECOMPILE) to the end of your query, so SQL Server does not cache the plan (which won't be re-used anyway).

You can run this DMV to see if you are experiencing this problem:

Code Snippet

-- Find the ad-hoc queries that are bloating the plan cache

SELECT TOP(1000) *

FROM sys.dm_Exec_cached_plans

CROSS APPLY sys.dm_exec_sql_text(plan_handle)

WHERE cacheobjtype = 'Compiled Plan'

AND objtype = 'Adhoc' AND usecounts = 1

--AND size_in_bytes < 200000

ORDER BY size_in_bytes DESC

I recommend that you build a stored procedure that has say 20 or 50 input parameters, then have a SELECT statement that uses an OR for each parameter. Then call the SP as many times as you need to. You can just use duplicates if you have less than 20 or 50 values for a call.

Code Snippet

SELECT id, c2

FROM t1

WHERE id = @.id1

OR id = @.id2

OR id = @.id3....

|||

Thanks a lot for your recommendations! Let me explain my insight:

There are different ways to avoid the IN with many arguments. One way is that Bushan shows (select and union all parameters internally) and

one way to use ORs. I will try both ways to check performance improvement.

But I still have some questions:

Limits of parameters / command length|||

The best way to compare two alternative ways of doing it is to run the queries back to back in SSMS, with SET STATISTICS IO enabled, and the graphical execution plan turned on. Then you can compare the cost the batches, and you will be able see the percentage cost of each batch, and compare the IO cost also.

If you want to take caching out of it, you can run DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE before each testing run, (but don't do that on a Production system).

Using a very big IN clause will really bloat your Procedure cache with single-use, ad-hoc query plans, which can really eat up some memory.

|||

Thanks for your advice, Glenn!

Hans-Peter

|||

Hi Hans-Peter,

are you building a DW or an OLTP system....

The usual case in DWs is to use generated integer keys all the time....

In OLTP using generated integer keys is often a good idea but it is by no means a hard and fast rule...

if you use integers like this then most queries are where clauses on attributes that link back to the integer...when you want to group things together and shorten where clauses you add attributes to group things.....

Best Regards

Performance question: View vs. Table

Hi all, I am new to the forum~

Suppose I have multiple tables, T1, T2, T3. I will use SELECT queries and apply AVG() and STDEV() on each of their columns, and average their results.

I can do this in two ways: one is to apply my SELECT multiple (3) times, then divide it by 3 in this case.

Another way is I create a View that UNIONS all T1 T2 T3, and apply AVG() and STDEV() on each columns.

Which solution is better? I mean, from a performance point of view. This is just a simplified version of my problem, and I would like to know what is the performance of using one (View) over the other (Table)... Does using View instead of table give me any performance overhead? Thanks everyone...They are the same solution. whether you use a select statment or a veiw you will be pulling data off the drives (or cache) to produce the answer. The view has the advantage of being pr-optimized. Views are generally used for this type of thing as it hides all the aggregation.

Don't know if this helped, Books Online has some interesting info look up views-SQL Server, overview and follow the hyper link to Scenarios for Using Views.sql

Performance question: Separate database or additional table?

We have a database that has about 50 tables, each with approximately 800K
rows. These tables are imports of data from another system that are updated
daily. All the tables have the same column, Id, for a primary key. The table
s
are accessed via views that serve mainly to assign meaningful names to the
columns and to insulate the applications from changes to the underlying
tables. In other words, the views are not restricting the users to a subset
of the columns in the tables, nor are they doing multi-table joins, etc. Thi
s
database is accessed by many users using many different applications across
the enterprise.
We have an application that queries a 12-view subset of the views, allowing
the user to do ad-hoc what-if queries with an application that generates SQL
queries from the user requests. The queries are often very time consuming. W
e
are attempting to improve the performance of this application. One thing we
know is that due to the nature of this application, 25% of the rows in the
views will never be selected and the application has to include SQL to
explicitly exlude this 25% of the rows.
One thought is to eliminate the scanning of the 25% of the rows that the
application never uses. There appear to be two ways of doing this:
1) Create another database containing only the 10-table/view subset and to
eliminate the unwanted rows at import/update time.
2) Create another table/view in the existing database with the Ids of the
75% of the rows that are used by this application and have the application d
o
a join on this table for all the queries and eliminate the SQL designed to
weed out the 25% of the rows that are not used by the application.
Whatever we choose to do, we have to live within the constraint of our
current hardware, so if we choose to create a separate database it will have
to share the current hardware with the other applications.
Given that the processing required to either load the alternate database or
maintain the 75% table is not an issue, which choice is likely to give us th
e
best increase in performance for the application?
Thanks,
BobHi
If you are currently seeing conflict between the two types of usage then it
is probably a good idea to create a second database for this to work on.
There will be a latency in the data unless depending on how you implement th
e
propogation of data.
I think you should look at the index usage on the database first, before you
implement any complex task to reduce the data.
John
"Bob" wrote:

> We have a database that has about 50 tables, each with approximately 800K
> rows. These tables are imports of data from another system that are update
d
> daily. All the tables have the same column, Id, for a primary key. The tab
les
> are accessed via views that serve mainly to assign meaningful names to the
> columns and to insulate the applications from changes to the underlying
> tables. In other words, the views are not restricting the users to a subse
t
> of the columns in the tables, nor are they doing multi-table joins, etc. T
his
> database is accessed by many users using many different applications acros
s
> the enterprise.
> We have an application that queries a 12-view subset of the views, allowin
g
> the user to do ad-hoc what-if queries with an application that generates S
QL
> queries from the user requests. The queries are often very time consuming.
We
> are attempting to improve the performance of this application. One thing w
e
> know is that due to the nature of this application, 25% of the rows in the
> views will never be selected and the application has to include SQL to
> explicitly exlude this 25% of the rows.
> One thought is to eliminate the scanning of the 25% of the rows that the
> application never uses. There appear to be two ways of doing this:
> 1) Create another database containing only the 10-table/view subset and to
> eliminate the unwanted rows at import/update time.
> 2) Create another table/view in the existing database with the Ids of the
> 75% of the rows that are used by this application and have the application
do
> a join on this table for all the queries and eliminate the SQL designed to
> weed out the 25% of the rows that are not used by the application.
> Whatever we choose to do, we have to live within the constraint of our
> current hardware, so if we choose to create a separate database it will ha
ve
> to share the current hardware with the other applications.
> Given that the processing required to either load the alternate database o
r
> maintain the 75% table is not an issue, which choice is likely to give us
the
> best increase in performance for the application?
> Thanks,
> Bob
>|||John,
"John Bell" wrote:

> Hi
> If you are currently seeing conflict between the two types of usage then
it
> is probably a good idea to create a second database for this to work on.
> There will be a latency in the data unless depending on how you implement
the
> propogation of data.
>
It's not really a conflict. The 75% part of the data represents currently
active items while the other 25% is effectively history. Items move from
current to history and new items are added, on a daily basis as the result o
f
batch processing on another system. This also eliminates data latency as an
issue.

> I think you should look at the index usage on the database first, before y
ou
> implement any complex task to reduce the data.
>
The column that determines whether an item is current or history is already
indexed. In addition, all the other columns used by the queries are also
indexed as appropriate.
Bob|||Assuming all other factors (indexing, joins, etc.) remain the same, I would
not expect removing 25% of the rows to impact the total runtime of the
queries that much. If 75% of the rows could be archived elsewhere, then that
would be significant. Rather than guessing, there are methods to know for
sure where the bottleneck is:
Investigate to what extent your indexes may be fragmented and defragment if
needed.
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Use the Show Execution Plan feature in Query Analyzer to see exactly how the
query optimizer is using your indexes.
http://support.microsoft.com/defaul...;243589&sd=tech
SQL Server hardware configuration and monitoring memory usage, IO
performance, etc:
/url]
[url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx#EE
AA" target="_blank">http://www.microsoft.com/technet/pr...px#EE
AA
"Bob" <notrainsley@.worldsavings.com> wrote in message
news:B41801C0-B505-4A2C-9E3B-CF4D5E7C5C9A@.microsoft.com...
> We have a database that has about 50 tables, each with approximately 800K
> rows. These tables are imports of data from another system that are
> updated
> daily. All the tables have the same column, Id, for a primary key. The
> tables
> are accessed via views that serve mainly to assign meaningful names to the
> columns and to insulate the applications from changes to the underlying
> tables. In other words, the views are not restricting the users to a
> subset
> of the columns in the tables, nor are they doing multi-table joins, etc.
> This
> database is accessed by many users using many different applications
> across
> the enterprise.
> We have an application that queries a 12-view subset of the views,
> allowing
> the user to do ad-hoc what-if queries with an application that generates
> SQL
> queries from the user requests. The queries are often very time consuming.
> We
> are attempting to improve the performance of this application. One thing
> we
> know is that due to the nature of this application, 25% of the rows in the
> views will never be selected and the application has to include SQL to
> explicitly exlude this 25% of the rows.
> One thought is to eliminate the scanning of the 25% of the rows that the
> application never uses. There appear to be two ways of doing this:
> 1) Create another database containing only the 10-table/view subset and to
> eliminate the unwanted rows at import/update time.
> 2) Create another table/view in the existing database with the Ids of the
> 75% of the rows that are used by this application and have the application
> do
> a join on this table for all the queries and eliminate the SQL designed to
> weed out the 25% of the rows that are not used by the application.
> Whatever we choose to do, we have to live within the constraint of our
> current hardware, so if we choose to create a separate database it will
> have
> to share the current hardware with the other applications.
> Given that the processing required to either load the alternate database
> or
> maintain the 75% table is not an issue, which choice is likely to give us
> the
> best increase in performance for the application?
> Thanks,
> Bob
>|||Hi
JT gave you a link regarding the query plans. Just because your archive flag
is indexed does not necessarily mean it is used or if there is a better inde
x
configuration. With ad-hoc queries you may not get brilliant query plans all
of the time, but you may be able to produce an indexing strategy that gives
very good responses most of the time. It could be that with changed indexes
there is no need to separate the system. If the system is only updated by a
batch job, then you can use a strategy where you drop the indexes before
inserting the new data and re-creating the indexes after. If you don't drop
the indexes they should be rebuilt after the bulk load anyhow.
You may want to read some of the article on
http://www.sql-server-performance.c...performance.asp regarding how
to improve performance.
One thing that I don't think has been mentioned is that you may gain some
benefit from having indexed views.
John
"Bob" wrote:

> John,
> "John Bell" wrote:
>
> It's not really a conflict. The 75% part of the data represents currently
> active items while the other 25% is effectively history. Items move from
> current to history and new items are added, on a daily basis as the result
of
> batch processing on another system. This also eliminates data latency as a
n
> issue.
>
> The column that determines whether an item is current or history is alread
y
> indexed. In addition, all the other columns used by the queries are also
> indexed as appropriate.
> Bob
>|||JT and John,
Thanks for your replies. One of the problems I had was trying to actually
grab some of the generated queries to check out the execution plan in Query
Analyzer.
I got one of the DBAs to do a trace on the processing and we grabbed a few
of the generated queries. We discovered that there was a view someone had
written that wasn't part of the standard load and was doing massive amounts
of calculations.
I'm going to write a procedure that will use that view to create another
table loaded with the results of the calculations that will run as part of
the nightly load. The view will then simply be returning data instead of
calculating the values over and over. This will change a series of repetitiv
e
compute intensive calculations into a simple inner join on a primary key.
Thanks again for everyone's help.
Bob

Performance question: Indexes on separate file group or dimension table on separate file?

Hi,
I have a set of disks available on my server (but 1 controller only). I want
to use it to improove queries performance...
I want to know what is better to improove the performance:
* moving all (non clustered) indexes on a separate file group on this set of
disk
* moving some tables on this file group (like dimension tables)
I'll monitor the queries to indentify if clustered indexes are more used
then standard indexes.
But I want to know what scenario, generally, helps the performance.
Thanks
Jerome.
Hi Jerome,
This is a difficult question to answer without knowing what kinds of tables
and indexes you're working with. For instance, if you find that you have a
lot of covering indexes for certain common queries, you might find that
moving those off to a different disk will improve performance -- that way
the other disk can satisfy those common queries and the disk with the
clustered index can satisfy other queries. Another consideration you
mentioned is dimension tables -- if you have a lot of large dimension
tables that get scanned during JOINs, you may find that moving them off to a
different disk than the fact tables will improve performance, as the disks
will be able to read the data in tandem. This is definitely something
you're going to have to experiment with on your end, I think.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:uuAKXt%23JFHA.1948@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have a set of disks available on my server (but 1 controller only). I
want
> to use it to improove queries performance...
> I want to know what is better to improove the performance:
> * moving all (non clustered) indexes on a separate file group on this set
of
> disk
> * moving some tables on this file group (like dimension tables)
> I'll monitor the queries to indentify if clustered indexes are more used
> then standard indexes.
> But I want to know what scenario, generally, helps the performance.
> Thanks
> Jerome.
>
|||ok...
in my case I have some small dimensions and only 1 "big" (100 000 rows)
my fact tables could have between 1 000 rows to 20 000 000 rows!
Generally the clustered index of each fact table contain all foreign keys
columns.
from your comments, there is no "default" recommandation.
So I'll done some tests I think...
thanks for your comments.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23bjLBTZKFHA.3184@.TK2MSFTNGP09.phx.gbl...
> Hi Jerome,
> This is a difficult question to answer without knowing what kinds of
> tables
> and indexes you're working with. For instance, if you find that you have
> a
> lot of covering indexes for certain common queries, you might find that
> moving those off to a different disk will improve performance -- that way
> the other disk can satisfy those common queries and the disk with the
> clustered index can satisfy other queries. Another consideration you
> mentioned is dimension tables -- if you have a lot of large dimension
> tables that get scanned during JOINs, you may find that moving them off to
> a
> different disk than the fact tables will improve performance, as the disks
> will be able to read the data in tandem. This is definitely something
> you're going to have to experiment with on your end, I think.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Jj" <willgart@.BBBhotmailAAA.com> wrote in message
> news:uuAKXt%23JFHA.1948@.TK2MSFTNGP14.phx.gbl...
> want
> of
>
|||"Jj" <willgart_A_@.hotmail_A_.com> wrote in message
news:eVltbdbKFHA.2132@.TK2MSFTNGP14.phx.gbl...
> ok...
> in my case I have some small dimensions and only 1 "big" (100 000 rows)
> my fact tables could have between 1 000 rows to 20 000 000 rows!
> Generally the clustered index of each fact table contain all foreign keys
> columns.
In my experience those small dimensions don't matter too much -- those pages
will end up in cache pretty quickly and usually won't go out of cache. It's
the big huge tables that cause the issues... Good luck tuning it!
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic

Performance question: Indexes on separate file group or dimension table on separate fi

Hi,
I have a set of disks available on my server (but 1 controller only). I want
to use it to improove queries performance...
I want to know what is better to improove the performance:
* moving all (non clustered) indexes on a separate file group on this set of
disk
* moving some tables on this file group (like dimension tables)
I'll monitor the queries to indentify if clustered indexes are more used
then standard indexes.
But I want to know what scenario, generally, helps the performance.
Thanks
Jerome.Hi Jerome,
This is a difficult question to answer without knowing what kinds of tables
and indexes you're working with. For instance, if you find that you have a
lot of covering indexes for certain common queries, you might find that
moving those off to a different disk will improve performance -- that way
the other disk can satisfy those common queries and the disk with the
clustered index can satisfy other queries. Another consideration you
mentioned is dimension tables -- if you have a lot of large dimension
tables that get scanned during JOINs, you may find that moving them off to a
different disk than the fact tables will improve performance, as the disks
will be able to read the data in tandem. This is definitely something
you're going to have to experiment with on your end, I think.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:uuAKXt%23JFHA.1948@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have a set of disks available on my server (but 1 controller only). I
want
> to use it to improove queries performance...
> I want to know what is better to improove the performance:
> * moving all (non clustered) indexes on a separate file group on this set
of
> disk
> * moving some tables on this file group (like dimension tables)
> I'll monitor the queries to indentify if clustered indexes are more used
> then standard indexes.
> But I want to know what scenario, generally, helps the performance.
> Thanks
> Jerome.
>|||ok...
in my case I have some small dimensions and only 1 "big" (100 000 rows)
my fact tables could have between 1 000 rows to 20 000 000 rows!
Generally the clustered index of each fact table contain all foreign keys
columns.
from your comments, there is no "default" recommandation.
So I'll done some tests I think...
thanks for your comments.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23bjLBTZKFHA.3184@.TK2MSFTNGP09.phx.gbl...
> Hi Jerome,
> This is a difficult question to answer without knowing what kinds of
> tables
> and indexes you're working with. For instance, if you find that you have
> a
> lot of covering indexes for certain common queries, you might find that
> moving those off to a different disk will improve performance -- that way
> the other disk can satisfy those common queries and the disk with the
> clustered index can satisfy other queries. Another consideration you
> mentioned is dimension tables -- if you have a lot of large dimension
> tables that get scanned during JOINs, you may find that moving them off to
> a
> different disk than the fact tables will improve performance, as the disks
> will be able to read the data in tandem. This is definitely something
> you're going to have to experiment with on your end, I think.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Jj" <willgart@.BBBhotmailAAA.com> wrote in message
> news:uuAKXt%23JFHA.1948@.TK2MSFTNGP14.phx.gbl...
> want
> of
>|||"Jj" <willgart_A_@.hotmail_A_.com> wrote in message
news:eVltbdbKFHA.2132@.TK2MSFTNGP14.phx.gbl...
> ok...
> in my case I have some small dimensions and only 1 "big" (100 000 rows)
> my fact tables could have between 1 000 rows to 20 000 000 rows!
> Generally the clustered index of each fact table contain all foreign keys
> columns.
In my experience those small dimensions don't matter too much -- those pages
will end up in cache pretty quickly and usually won't go out of cache. It's
the big huge tables that cause the issues... Good luck tuning it!
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--sql

performance question/multiple keys

Hi All,
The table I'm using has full-text columns and also a date column thats
indexed. If I do a query on a date range and the full-text column does SQL
Server return the full-text rows first, then subset by date, or subset by
date and pass that set of rows to MSSEARCH?
I'm wondering about performance issues on tables with many rows (3 millions
or so). I'm wondering if i should break the data up into tables by day so
that i'm not doing full-text searches if I know that I'll be getting a small
subset according to a date range.
Any insight on this issue?
thanks,
John
Rows are first returned from MSSearch and then trimmed.
Partitioning is a good idea. However, how large are your results sets? If
they are small (i.e. under 500 rows) this should not be a problem.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John Mott" <johnmott59@.hotmail.com> wrote in message
news:eBaJxILvFHA.3080@.tk2msftngp13.phx.gbl...
> Hi All,
> The table I'm using has full-text columns and also a date column thats
> indexed. If I do a query on a date range and the full-text column does SQL
> Server return the full-text rows first, then subset by date, or subset by
> date and pass that set of rows to MSSEARCH?
> I'm wondering about performance issues on tables with many rows (3
millions
> or so). I'm wondering if i should break the data up into tables by day so
> that i'm not doing full-text searches if I know that I'll be getting a
small
> subset according to a date range.
> Any insight on this issue?
> thanks,
> John
>
|||John,
First of all, it is always a good idea to get the SQL Server & OS platform
version info. Could you post the full output of SELECT @.@.version ?
Q. If I do a query on a date range and the full-text column does SQL Server
return the full-text rows first, then subset by date, or subset by date and
pass that set of rows to MSSEARCH?
A. SQL Server first queries the MSSearch service for all rows that match the
FTS query, then applies the WHERE clause filter after ALL results are
returned from the FT Catalog.
Yes, there can be performance issues with SQL Server 2000, but on which side
of the equation (FT Indexing &/or FT Search) - running a Full Population vs
running CONTAINS query are you concerned with? If the former, see the below
blog entry detailed resources.Also, review SQL Server 2000 BOL Title
"Full-text Search Recommendations". If the latter, you should review KB
article 240833 (Q240833) "FIX: Full-Text Search Performance Improved via
Support for TOP" and consider using the Top_N_by_Rank with either
CONTAINSTABLE or FREETEXTTABLE. If possible, partitioning the table into
smaller table can be helpful.
SQL Server 2000 Full-Text Search Resources and Links
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"John Mott" <johnmott59@.hotmail.com> wrote in message
news:eBaJxILvFHA.3080@.tk2msftngp13.phx.gbl...
> Hi All,
> The table I'm using has full-text columns and also a date column thats
> indexed. If I do a query on a date range and the full-text column does SQL
> Server return the full-text rows first, then subset by date, or subset by
> date and pass that set of rows to MSSEARCH?
> I'm wondering about performance issues on tables with many rows (3
> millions
> or so). I'm wondering if i should break the data up into tables by day so
> that i'm not doing full-text searches if I know that I'll be getting a
> small
> subset according to a date range.
> Any insight on this issue?
> thanks,
> John
>

Performance question.

Hello Everybody,
I have a table which has arround 30 millions rows.
Table structure is as following..
CREATE TABLE TestTable
(Id INT, --which is PK,
EmpId INT, --There is a non cluster index on it.
DeptName VARCHAR(50),
Hours NUMERIC(5,2),
Tdate DATETIME,
ProjectNumber smallint,
.and few more columns
.
.
)
And i have following query, which is taking arround 1 minute 10 sec to run.
SELECT
DeptName,
EmpId,
SUM(CASE WHEN ProjectNumber = 11 THEN Hours ELSE 0
END) AS FinHours,
SUM(CASE WHEN ProjectId = 12 THEN Hours ELSE 0
END) AS HrHours,
SUM(CASE WHEN ProjectId = 13 THEN Hours ELSE 0
END) AS TaxHours,
FROM TestTable WHERE Tdate between @.Date1 and @.Date2
GROUP BY
DeptName,
EmpId
I do not have index on ProjectNumber column because this column will have
only
200 distinct values.
If i create index on Group by Columns, would it improve performance ?
Pls let me know, how can i imporve performance ?
Thanks.I don't know about the rest of your queries or your usage patterns, but the
most obvious choice in this case is to make the PK nonclustered and create a
clustered index on the Tdate column to support your WHERE clause.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:569B7FD3-452C-4B8F-9056-06798A32B3EA@.microsoft.com...
> Hello Everybody,
> I have a table which has arround 30 millions rows.
> Table structure is as following..
> CREATE TABLE TestTable
> (Id INT, --which is PK,
> EmpId INT, --There is a non cluster index on it.
> DeptName VARCHAR(50),
> Hours NUMERIC(5,2),
> Tdate DATETIME,
> ProjectNumber smallint,
> .and few more columns
> .
> .
> )
> And i have following query, which is taking arround 1 minute 10 sec to
> run.
>
> SELECT
> DeptName,
> EmpId,
> SUM(CASE WHEN ProjectNumber = 11 THEN Hours ELSE 0
> END) AS FinHours,
> SUM(CASE WHEN ProjectId = 12 THEN Hours ELSE 0
> END) AS HrHours,
> SUM(CASE WHEN ProjectId = 13 THEN Hours ELSE 0
> END) AS TaxHours,
> FROM TestTable WHERE Tdate between @.Date1 and @.Date2
> GROUP BY
> DeptName,
> EmpId
> I do not have index on ProjectNumber column because this column will have
> only
> 200 distinct values.
> If i create index on Group by Columns, would it improve performance ?
> Pls let me know, how can i imporve performance ?
>
> Thanks.|||also you may try an index on all the columns involved in the query,
Tdate first if the interval is narrow, DeptName, EmpId first if the
interval is wide|||Your query indicates that you are using the following columns: DeptId,
EmpId, ProjectNumber, ProjectId, TDate. This means that SQL Server will have
to look at all of the rows being returned regardless of whether an index
exists on your grouped columns. This will be the case unless you were to
create a covering index for all of the columns being returned. In your case,
that's a lot of columns so I don't recommend it.
According to your DDL, I don't see an index on Tdate. I would actually start
with that. However, depending on the number of rows that are being returned
from your query, the optimizer may or may not even choose to use that index
(due to the expense of bookmark lookup). However, I would attempt a
non-clustered index on Tdate first.
Assuming you're not using the data for anything else (or much else), Adam's
method could be the best route. However, this would result in larger indexes
for all of the nonclustered indexes on this table.
Since all nonclustered indexes also include the clustered index key, and
your key is going from a 4-byte data type to an 8-byte data type and add up
114MB to each index on your table. Combined with the fact that a
"uniquifier" is applied to all non-unique clustered indexes, could add
another 4 bytes to your rows and bring each nonclustered index up to 228 MB.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%237v%239uXBGHA.892@.TK2MSFTNGP12.phx.gbl...
>I don't know about the rest of your queries or your usage patterns, but the
>most obvious choice in this case is to make the PK nonclustered and create
>a clustered index on the Tdate column to support your WHERE clause.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "mvp" <mvp@.discussions.microsoft.com> wrote in message
> news:569B7FD3-452C-4B8F-9056-06798A32B3EA@.microsoft.com...
>|||"Jared Ko" <JaredKo05@.sanbeiji.removethispart.com> wrote in message
news:uPOE8AZBGHA.736@.TK2MSFTNGP10.phx.gbl...
> Since all nonclustered indexes also include the clustered index key, and
> your key is going from a 4-byte data type to an 8-byte data type and add
> up 114MB to each index on your table. Combined with the fact that a
> "uniquifier" is applied to all non-unique clustered indexes, could add
> another 4 bytes to your rows and bring each nonclustered index up to 228
> MB.
Slight correction: The uniquifier is only added to non-unique rows, not
every row. So if the majority are unique (which we might expect from a
DATETIME column), the uniquifier will add very little overhead.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||Thanks Adam. I was frantically searching for that information while I was
writing my response. I was thinking that was the case but a couple of web
sites I hit suggested otherwise.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OybGaZZBGHA.2356@.tk2msftngp13.phx.gbl...
> "Jared Ko" <JaredKo05@.sanbeiji.removethispart.com> wrote in message
> news:uPOE8AZBGHA.736@.TK2MSFTNGP10.phx.gbl...
> Slight correction: The uniquifier is only added to non-unique rows, not
> every row. So if the majority are unique (which we might expect from a
> DATETIME column), the uniquifier will add very little overhead.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
>|||Best is to test for yourself. Have to similar tables, populate them with bun
ch of rows. One unique,
the other all with same value. Check size of the index. that is how I conclu
ded that uniqifier is
only added for the duplicates.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jared Ko" <JaredKo05@.sanbeiji.removethispart.com> wrote in message
news:uyOPGjZBGHA.3984@.TK2MSFTNGP14.phx.gbl...
> Thanks Adam. I was frantically searching for that information while I was
writing my response. I
> was thinking that was the case but a couple of web sites I hit suggested o
therwise.
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:OybGaZZBGHA.2356@.tk2msftngp13.phx.gbl...
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OK1JSoZBGHA.3472@.TK2MSFTNGP09.phx.gbl...
> Best is to test for yourself. Have to similar tables, populate them with
> bunch of rows. One unique, the other all with same value. Check size of
> the index. that is how I concluded that uniqifier is only added for the
> duplicates.
I took the lazy way out. _Inside SQL Server 2000_, page 412:
"If your clustered index was not created with the UNIQUE property, SQL
Server adds a 4-byte field when necessary to make each key unique."
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||Just a question - wouldn't horizontal partitioning be useful in this case?
of course apart from index on the Tdate field.
Peter

performance question

Hi,
What's the best way to store a certain kind of information in an SQL-server database:
-having one table with many records (e.g. millions)
or
-having many tables (e.g. thousands) with less records (e.g. thousands)Your question cannot be answered with a simple "statement x is correct" as the answer varies wildly based on the scenario. The best way to find an answer is to do some reading. I suggest you start by reading up on normalisation (www.r937.com/relational.html).|||Ah, the ever popular answer: It depends!

With one pseudo-exception I'll explain in a moment, if you have one single type of thing, then one table is the best way to store it. If you have one thosand similar but slightly different things, then one thousand tables are usually better.

The one place that I'll make an exception to that rule (which is really just a clarification, not a true exception) is that lookups are a different kind of beast. While people sometimes argue that all lookups belong in a single table (this is known as One True Lookup Table or OTLT), lookups really belong in separate tables.

If you have 96 kinds of lookup items, those are really 96 quite different things. If you can put hat sizes and truck colors into a lookup table, but you can't exchange a hat size for a truck color (no one I know wears a "Sunburnt Orange" size hat or drives a 6 7/8 colored truck), then the hat sizes are a different thing than the truck colors. Because they are different things, they ought to be in a different table.

-PatP

Performance question

Good afternoon,

I'd like to know what you guys think about this performance question. In order to describe it well, I'll layout a simlpe table, and query, and I'll explain the question after that.

[Table: Categories]
Id [PK]
Alias NVarChar(50) [ASC Index]

[Stored Procedure Query]

SELECT C.IdAS Id,C.AliasAS CategoryFROMCategories CORDER BYC.AliasASC;

My question is simple. Let's supose we are getting a good sum of rows, like 10.000, to fill out a list. Even though that wouldn't be the best practice, because we should implement some sort of paging, we'll just ignore that fact for the sake of simplicity.

The question is: What would perform better? The query that I've writen above or a query that was exactly the same but instead of having Order By C.Alias ASC we would have Order BY Category ASC (being Category an alias created in the select statement)?

I know this might be seen as a dumb non-sense question... But still, I was just wondering if anyone knew the answer.

Best regards,
DBA

Hi,

the Index will do the job?

with proper indexing, the result will be noticible

with no index and ordering sorting will suffer

IMHO

Performance Question

Hi All
I have a database of students which contains a table StoredCvs. This
contains all of their CVs (Resume's) and has a full text index stored in a
"StoredFilesCatalog". It is unlikely that this table will grow to more than
1 million rows although it is possible.
I now want to start storing files (for searching) for other types of records
in our system i.e. company files, project files, other candidate files etc.
etc. I am now faced with a few choices and wandered which would be the best
one. I have decided not to create another catalog at this stage as I feel
this could be done later if performance is really bad. However, I was
wandering what the difference would be between: a) Creating another table
(StoredFiles), which uses the "StoredFilesCatalog", but would have a
seperate fulltextindex. b) Add a FileTypeID column to my StoredCVs table
(would rename this to StoredFiles) and store all files in the one table
(with one fulltextindex).
The main function of the system is to search Candidate Cvs (Resume's) so I
was wandering if there is any performance advantage of having 2 tables or if
the searches would be the same as they both use the same
"StoredFilesCatalog" anyway.
Hope this makes sense and really appreciate any advice.
Cheers
Joe
You will get better performance with two catalogs - one for each table. You
could add a separate column for FileType but this could be problematic if
you are using top_n_by_Rank
For instance suppose you do a query like this
Select * from StoredCvs join containstable(StoredCvs,*,'microsoft',200) as T
on T.[Key]=StoredCvs.PK
where filetype='resume'
order by rank desc
If the first 200 hits returned were all not of the fileType resume, you
would get no hits, even though there could be matches that might not occur
in the first 200 hits.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Joe Zammit" <zammit_joe@.hotmail.com> wrote in message
news:u619ySFBFHA.824@.TK2MSFTNGP11.phx.gbl...
> Hi All
> I have a database of students which contains a table StoredCvs. This
> contains all of their CVs (Resume's) and has a full text index stored in a
> "StoredFilesCatalog". It is unlikely that this table will grow to more
than
> 1 million rows although it is possible.
> I now want to start storing files (for searching) for other types of
records
> in our system i.e. company files, project files, other candidate files
etc.
> etc. I am now faced with a few choices and wandered which would be the
best
> one. I have decided not to create another catalog at this stage as I feel
> this could be done later if performance is really bad. However, I was
> wandering what the difference would be between: a) Creating another table
> (StoredFiles), which uses the "StoredFilesCatalog", but would have a
> seperate fulltextindex. b) Add a FileTypeID column to my StoredCVs table
> (would rename this to StoredFiles) and store all files in the one table
> (with one fulltextindex).
> The main function of the system is to search Candidate Cvs (Resume's) so I
> was wandering if there is any performance advantage of having 2 tables or
if
> the searches would be the same as they both use the same
> "StoredFilesCatalog" anyway.
> Hope this makes sense and really appreciate any advice.
> Cheers
> Joe
>
|||Joe,
So, I can be sure of your environment, could you also post the full output
of -- SELECT @.@.version -- as this is most helpful in understanding your
environment. As I understand it you have one table: "StoredCvs with <1
million rows and one FT Catalog:StoredFilesCatalog", and you've decided not
to create another FT Catalog. Note, you can only have one FT Catalog defined
per table, but that one FT Catalog can support multiple column per table as
well as multiple tables.
Your decision is between the two option (a or b) below. Correct?
a) Creating another table (StoredFiles), which uses the
"StoredFilesCatalog", but would have a separate fulltextindex
b) Add a FileTypeID column to my StoredCVs table (would rename this to
StoredFiles) and store all files in the one table (with one fulltextindex).
If I have your environment correct, I'd recommend option a - create another
table (StoredFiles). Primarily because, SQL Server 2000 FT Catalogs start to
have performance issues with SQL Server 2000 tables at approx. 1 million
rows (still functional, but just need performance tuning) and adding
addition file types and larger files to your existing table (StoredCvs) will
cause it to grow above the 1 million row threshold. See SQL Server 2000 BOL
title "Full-text Search Recommendations" for more information on performance
tuning FT Catalogs on tables with more than 1 million rows.
Another issue/question that you did not mention is whether or not these two
tables will be often (always, sometimes, never) joined together in common
queries or in common FTS queries. If they are seldom or never joined in
frequently used queries, then it makes more sense for their to be separate
tables.
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Joe Zammit" <zammit_joe@.hotmail.com> wrote in message
news:u619ySFBFHA.824@.TK2MSFTNGP11.phx.gbl...
> Hi All
> I have a database of students which contains a table StoredCvs. This
> contains all of their CVs (Resume's) and has a full text index stored in a
> "StoredFilesCatalog". It is unlikely that this table will grow to more
than
> 1 million rows although it is possible.
> I now want to start storing files (for searching) for other types of
records
> in our system i.e. company files, project files, other candidate files
etc.
> etc. I am now faced with a few choices and wandered which would be the
best
> one. I have decided not to create another catalog at this stage as I feel
> this could be done later if performance is really bad. However, I was
> wandering what the difference would be between: a) Creating another table
> (StoredFiles), which uses the "StoredFilesCatalog", but would have a
> seperate fulltextindex. b) Add a FileTypeID column to my StoredCVs table
> (would rename this to StoredFiles) and store all files in the one table
> (with one fulltextindex).
> The main function of the system is to search Candidate Cvs (Resume's) so I
> was wandering if there is any performance advantage of having 2 tables or
if
> the searches would be the same as they both use the same
> "StoredFilesCatalog" anyway.
> Hope this makes sense and really appreciate any advice.
> Cheers
> Joe
>
|||Good Point!
I was thinking along the 2 table line anyway so thanks for your help.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OJX5EQIBFHA.1392@.TK2MSFTNGP14.phx.gbl...
> You will get better performance with two catalogs - one for each table.
You
> could add a separate column for FileType but this could be problematic if
> you are using top_n_by_Rank
> For instance suppose you do a query like this
> Select * from StoredCvs join containstable(StoredCvs,*,'microsoft',200) as
T[vbcol=seagreen]
> on T.[Key]=StoredCvs.PK
> where filetype='resume'
> order by rank desc
> If the first 200 hits returned were all not of the fileType resume, you
> would get no hits, even though there could be matches that might not occur
> in the first 200 hits.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "Joe Zammit" <zammit_joe@.hotmail.com> wrote in message
> news:u619ySFBFHA.824@.TK2MSFTNGP11.phx.gbl...
a[vbcol=seagreen]
> than
> records
> etc.
> best
feel[vbcol=seagreen]
table[vbcol=seagreen]
I[vbcol=seagreen]
or
> if
>
sql

Performance Question

Hi!
If I have a table in SQL 2000 with 500,000 registry and I do one query to
that table, what shall be the answer time? Is to slow? Should I separate the
data for more tables?
I am structuralizing one db.
Thanks
Albano Alves
Impossible to answer. It depends on:
A) What the datatypes of your columns are / how many columns there are / how
"wide" the table is
B) What indexes are created on the table / how the indexes are being used /
whether statistics are up to date
C) What kind of hardware you have: Disks / disk configuration / memory /
processor
D) What other activity is happening on the server
So to answer your question: Test it on your end.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Albano Alves" <albano.alves@.vpconsulting.pt> wrote in message
news:egT7h69IFHA.1860@.TK2MSFTNGP15.phx.gbl...
> Hi!
> If I have a table in SQL 2000 with 500,000 registry and I do one query to
> that table, what shall be the answer time? Is to slow? Should I separate
the
> data for more tables?
> I am structuralizing one db.
> Thanks
> Albano Alves
>
|||This depends upon a lot of factors such as the where query the index's on
your whether its using a cursor table, the speed of your hard disks, the
state of fragmentation,of your data files, and so in.
Please post the table structure then the query so we can have a look at it
and sugest improvments.
Formally 'Peter The Spate'
"All generalizations are false, including this one."
Mark Twain
"Albano Alves" wrote:

> Hi!
> If I have a table in SQL 2000 with 500,000 registry and I do one query to
> that table, what shall be the answer time? Is to slow? Should I separate the
> data for more tables?
> I am structuralizing one db.
> Thanks
> Albano Alves
>
>
|||I have more or less 20 field (varchar and int), and the ID can be related
with another Table. The server is a good machine, but it will have many
users, more or less 1,000
In my tests I feel that how much bigger will be I number it of returned
data, minor is the performance and that when to only return a small one
number of registers the performance is good. That is truth?
My debt is if I should have one alone table for, suppliers, customers and
all stakeholders... or some tables, one for each stakeholder.
Thanks
Albano Alves
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> escreveu na mensagem
news:eCOVBj%23IFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Impossible to answer. It depends on:
> A) What the datatypes of your columns are / how many columns there are /
> how
> "wide" the table is
> B) What indexes are created on the table / how the indexes are being used
> /
> whether statistics are up to date
> C) What kind of hardware you have: Disks / disk configuration / memory /
> processor
> D) What other activity is happening on the server
> So to answer your question: Test it on your end.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Albano Alves" <albano.alves@.vpconsulting.pt> wrote in message
> news:egT7h69IFHA.1860@.TK2MSFTNGP15.phx.gbl...
> the
>
|||"Albano Alves" <albano.alves@.vpconsulting.pt> wrote in message
news:ejb%23uF$IFHA.2844@.TK2MSFTNGP10.phx.gbl...
> My debt is if I should have one alone table for, suppliers, customers and
> all stakeholders... or some tables, one for each stakeholder.
I don't recommend that -- it will mean that your application or stored
procedure will have to figure out what table to query at runtime, or you'll
have to have a bunch of partitioned views that you update every time data
changes. It will be a maintenence nightmare. Try to solve issues with
indexes first.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic