OpenXML Identity Insert: table

Showing posts with label table. Show all posts

Friday, March 30, 2012

Optimizing table with more than 54 million records

I have a table that has more than 54 million records and I'm searching
records using the LIKE statement. I looking for ways to
optimize/partition/etc. this table.
This is the table structure:
TABLE "SEARCHCACHE"
Fields:
- searchType int
- searchField int
- value varchar(500)
- externalKey int
For example, a simple search would be:
*******
SELECT TOP 100 *
FROM SearchCache
WHERE (searchType = 2) AND (searchField = 2) AND (value LIKE 'name1%' OR
value LIKE 'name2%')
*******
This works just fine, it retrieves records in 2-3 seconds. The problem
is when I combine more predicates. For example:
*******
SELECT TOP 100 *
FROM SearchCache
WHERE ((searchType = 2) AND (searchField = 2) AND (value LIKE 'name1%'
OR value LIKE 'name2%'))
OR ((searchType = 2) AND (searchField = 3) AND (value LIKE
'anothervalue1%' OR value LIKE 'anothervalue2%'))
*******
this may take up to 30-40 seconds!
Any suggestion?
Thanks in advanced.
On Jul 30, 1:23 pm, Gaspar <gas...@.no-reply.com> wrote:
> I have a table that has more than 54 million records and I'm searching
> records using the LIKE statement. I looking for ways to
> optimize/partition/etc. this table.
> This is the table structure:
> TABLE "SEARCHCACHE"
> Fields:
> - searchType int
> - searchField int
> - value varchar(500)
> - externalKey int
> For example, a simple search would be:
> *******
> SELECT TOP 100 *
> FROM SearchCache
> WHERE (searchType = 2) AND (searchField = 2) AND (value LIKE 'name1%' OR
> value LIKE 'name2%')
> *******
> This works just fine, it retrieves records in 2-3 seconds. The problem
> is when I combine more predicates. For example:
> *******
> SELECT TOP 100 *
> FROM SearchCache
> WHERE ((searchType = 2) AND (searchField = 2) AND (value LIKE 'name1%'
> OR value LIKE 'name2%'))
> OR ((searchType = 2) AND (searchField = 3) AND (value LIKE
> 'anothervalue1%' OR value LIKE 'anothervalue2%'))
> *******
> this may take up to 30-40 seconds!
> Any suggestion?
> Thanks in advanced.
maybe you could consider full-text search on the table? this would
require afair rewriting queries though.
|||The original solution used FullText but it didn't work for my scenario.
That's why I implemented this SearchCache table.
Thanks.
Piotr Rodak wrote:
> On Jul 30, 1:23 pm, Gaspar <gas...@.no-reply.com> wrote:
> maybe you could consider full-text search on the table? this would
> require afair rewriting queries though.
>
|||Gaspar
1) Don't use TOP 100 command especially without ORDER BY clause
2) Please provide some details from an execution plan
"Gaspar" <gaspar@.no-reply.com> wrote in message
news:e1xYASq0HHA.4824@.TK2MSFTNGP02.phx.gbl...
>I have a table that has more than 54 million records and I'm searching
> records using the LIKE statement. I looking for ways to
> optimize/partition/etc. this table.
> This is the table structure:
> TABLE "SEARCHCACHE"
> Fields:
> - searchType int
> - searchField int
> - value varchar(500)
> - externalKey int
> For example, a simple search would be:
> *******
> SELECT TOP 100 *
> FROM SearchCache
> WHERE (searchType = 2) AND (searchField = 2) AND (value LIKE 'name1%' OR
> value LIKE 'name2%')
> *******
> This works just fine, it retrieves records in 2-3 seconds. The problem
> is when I combine more predicates. For example:
> *******
> SELECT TOP 100 *
> FROM SearchCache
> WHERE ((searchType = 2) AND (searchField = 2) AND (value LIKE 'name1%'
> OR value LIKE 'name2%'))
> OR ((searchType = 2) AND (searchField = 3) AND (value LIKE
> 'anothervalue1%' OR value LIKE 'anothervalue2%'))
> *******
> this may take up to 30-40 seconds!
> Any suggestion?
> Thanks in advanced.
|||Gaspar,
Try unioning the result from independent statements.
select top 100 *
from
(
SELECT TOP 100 *
FROM SearchCache
WHERE ((searchType = 2) AND (searchField = 2) AND (value LIKE 'name1%'
OR value LIKE 'name2%'))
union
SELECT TOP 100 *
FROM SearchCache
where ((searchType = 2) AND (searchField = 3) AND (value LIKE
'anothervalue1%' OR value LIKE 'anothervalue2%'))
) as t
AMB
"Gaspar" wrote:

> I have a table that has more than 54 million records and I'm searching
> records using the LIKE statement. I looking for ways to
> optimize/partition/etc. this table.
> This is the table structure:
> TABLE "SEARCHCACHE"
> Fields:
> - searchType int
> - searchField int
> - value varchar(500)
> - externalKey int
> For example, a simple search would be:
> *******
> SELECT TOP 100 *
> FROM SearchCache
> WHERE (searchType = 2) AND (searchField = 2) AND (value LIKE 'name1%' OR
> value LIKE 'name2%')
> *******
> This works just fine, it retrieves records in 2-3 seconds. The problem
> is when I combine more predicates. For example:
> *******
> SELECT TOP 100 *
> FROM SearchCache
> WHERE ((searchType = 2) AND (searchField = 2) AND (value LIKE 'name1%'
> OR value LIKE 'name2%'))
> OR ((searchType = 2) AND (searchField = 3) AND (value LIKE
> 'anothervalue1%' OR value LIKE 'anothervalue2%'))
> *******
> this may take up to 30-40 seconds!
> Any suggestion?
> Thanks in advanced.
>
|||You may want to try running the profiler and check suggestions under the
tuning wizard for the tables - it could be a lack of a good index.
Regards,
Jamie
"Gaspar" wrote:

> I have a table that has more than 54 million records and I'm searching
> records using the LIKE statement. I looking for ways to
> optimize/partition/etc. this table.
> This is the table structure:
> TABLE "SEARCHCACHE"
> Fields:
> - searchType int
> - searchField int
> - value varchar(500)
> - externalKey int
> For example, a simple search would be:
> *******
> SELECT TOP 100 *
> FROM SearchCache
> WHERE (searchType = 2) AND (searchField = 2) AND (value LIKE 'name1%' OR
> value LIKE 'name2%')
> *******
> This works just fine, it retrieves records in 2-3 seconds. The problem
> is when I combine more predicates. For example:
> *******
> SELECT TOP 100 *
> FROM SearchCache
> WHERE ((searchType = 2) AND (searchField = 2) AND (value LIKE 'name1%'
> OR value LIKE 'name2%'))
> OR ((searchType = 2) AND (searchField = 3) AND (value LIKE
> 'anothervalue1%' OR value LIKE 'anothervalue2%'))
> *******
> this may take up to 30-40 seconds!
> Any suggestion?
> Thanks in advanced.
>

Optimizing table with more than 54 million records

I have a table that has more than 54 million records and I'm searching
records using the LIKE statement. I looking for ways to
optimize/partition/etc. this table.
This is the table structure:
TABLE "SEARCHCACHE"
Fields:
- searchType int
- searchField int
- value varchar(500)
- externalKey int
For example, a simple search would be:
*******
SELECT TOP 100 *
FROM SearchCache
WHERE (searchType = 2) AND (searchField = 2) AND (value LIKE 'name1%' OR
value LIKE 'name2%')
*******
This works just fine, it retrieves records in 2-3 seconds. The problem
is when I combine more predicates. For example:
*******
SELECT TOP 100 *
FROM SearchCache
WHERE ((searchType = 2) AND (searchField = 2) AND (value LIKE 'name1%'
OR value LIKE 'name2%'))
OR ((searchType = 2) AND (searchField = 3) AND (value LIKE
'anothervalue1%' OR value LIKE 'anothervalue2%'))
*******
this may take up to 30-40 seconds!
Any suggestion?
Thanks in advanced.On Jul 30, 1:23 pm, Gaspar <gas...@.no-reply.com> wrote:
> I have a table that has more than 54 million records and I'm searching
> records using the LIKE statement. I looking for ways to
> optimize/partition/etc. this table.
> This is the table structure:
> TABLE "SEARCHCACHE"
> Fields:
> - searchType int
> - searchField int
> - value varchar(500)
> - externalKey int
> For example, a simple search would be:
> *******
> SELECT TOP 100 *
> FROM SearchCache
> WHERE (searchType = 2) AND (searchField = 2) AND (value LIKE 'name1%' OR
> value LIKE 'name2%')
> *******
> This works just fine, it retrieves records in 2-3 seconds. The problem
> is when I combine more predicates. For example:
> *******
> SELECT TOP 100 *
> FROM SearchCache
> WHERE ((searchType = 2) AND (searchField = 2) AND (value LIKE 'name1%'
> OR value LIKE 'name2%'))
> OR ((searchType = 2) AND (searchField = 3) AND (value LIKE
> 'anothervalue1%' OR value LIKE 'anothervalue2%'))
> *******
> this may take up to 30-40 seconds!
> Any suggestion?
> Thanks in advanced.
maybe you could consider full-text search on the table? this would
require afair rewriting queries though.|||The original solution used FullText but it didn't work for my scenario.
That's why I implemented this SearchCache table.
Thanks.
Piotr Rodak wrote:
> On Jul 30, 1:23 pm, Gaspar <gas...@.no-reply.com> wrote:
>> I have a table that has more than 54 million records and I'm searching
>> records using the LIKE statement. I looking for ways to
>> optimize/partition/etc. this table.
>> This is the table structure:
>> TABLE "SEARCHCACHE"
>> Fields:
>> - searchType int
>> - searchField int
>> - value varchar(500)
>> - externalKey int
>> For example, a simple search would be:
>> *******
>> SELECT TOP 100 *
>> FROM SearchCache
>> WHERE (searchType = 2) AND (searchField = 2) AND (value LIKE 'name1%' OR
>> value LIKE 'name2%')
>> *******
>> This works just fine, it retrieves records in 2-3 seconds. The problem
>> is when I combine more predicates. For example:
>> *******
>> SELECT TOP 100 *
>> FROM SearchCache
>> WHERE ((searchType = 2) AND (searchField = 2) AND (value LIKE 'name1%'
>> OR value LIKE 'name2%'))
>> OR ((searchType = 2) AND (searchField = 3) AND (value LIKE
>> 'anothervalue1%' OR value LIKE 'anothervalue2%'))
>> *******
>> this may take up to 30-40 seconds!
>> Any suggestion?
>> Thanks in advanced.
> maybe you could consider full-text search on the table? this would
> require afair rewriting queries though.
>|||Gaspar
1) Don't use TOP 100 command especially without ORDER BY clause
2) Please provide some details from an execution plan
"Gaspar" <gaspar@.no-reply.com> wrote in message
news:e1xYASq0HHA.4824@.TK2MSFTNGP02.phx.gbl...
>I have a table that has more than 54 million records and I'm searching
> records using the LIKE statement. I looking for ways to
> optimize/partition/etc. this table.
> This is the table structure:
> TABLE "SEARCHCACHE"
> Fields:
> - searchType int
> - searchField int
> - value varchar(500)
> - externalKey int
> For example, a simple search would be:
> *******
> SELECT TOP 100 *
> FROM SearchCache
> WHERE (searchType = 2) AND (searchField = 2) AND (value LIKE 'name1%' OR
> value LIKE 'name2%')
> *******
> This works just fine, it retrieves records in 2-3 seconds. The problem
> is when I combine more predicates. For example:
> *******
> SELECT TOP 100 *
> FROM SearchCache
> WHERE ((searchType = 2) AND (searchField = 2) AND (value LIKE 'name1%'
> OR value LIKE 'name2%'))
> OR ((searchType = 2) AND (searchField = 3) AND (value LIKE
> 'anothervalue1%' OR value LIKE 'anothervalue2%'))
> *******
> this may take up to 30-40 seconds!
> Any suggestion?
> Thanks in advanced.|||Gaspar,
Try unioning the result from independent statements.
select top 100 *
from
(
SELECT TOP 100 *
FROM SearchCache
WHERE ((searchType = 2) AND (searchField = 2) AND (value LIKE 'name1%'
OR value LIKE 'name2%'))
union
SELECT TOP 100 *
FROM SearchCache
where ((searchType = 2) AND (searchField = 3) AND (value LIKE
'anothervalue1%' OR value LIKE 'anothervalue2%'))
) as t
AMB
"Gaspar" wrote:
> I have a table that has more than 54 million records and I'm searching
> records using the LIKE statement. I looking for ways to
> optimize/partition/etc. this table.
> This is the table structure:
> TABLE "SEARCHCACHE"
> Fields:
> - searchType int
> - searchField int
> - value varchar(500)
> - externalKey int
> For example, a simple search would be:
> *******
> SELECT TOP 100 *
> FROM SearchCache
> WHERE (searchType = 2) AND (searchField = 2) AND (value LIKE 'name1%' OR
> value LIKE 'name2%')
> *******
> This works just fine, it retrieves records in 2-3 seconds. The problem
> is when I combine more predicates. For example:
> *******
> SELECT TOP 100 *
> FROM SearchCache
> WHERE ((searchType = 2) AND (searchField = 2) AND (value LIKE 'name1%'
> OR value LIKE 'name2%'))
> OR ((searchType = 2) AND (searchField = 3) AND (value LIKE
> 'anothervalue1%' OR value LIKE 'anothervalue2%'))
> *******
> this may take up to 30-40 seconds!
> Any suggestion?
> Thanks in advanced.
>|||You may want to try running the profiler and check suggestions under the
tuning wizard for the tables - it could be a lack of a good index.
--
Regards,
Jamie
"Gaspar" wrote:
> I have a table that has more than 54 million records and I'm searching
> records using the LIKE statement. I looking for ways to
> optimize/partition/etc. this table.
> This is the table structure:
> TABLE "SEARCHCACHE"
> Fields:
> - searchType int
> - searchField int
> - value varchar(500)
> - externalKey int
> For example, a simple search would be:
> *******
> SELECT TOP 100 *
> FROM SearchCache
> WHERE (searchType = 2) AND (searchField = 2) AND (value LIKE 'name1%' OR
> value LIKE 'name2%')
> *******
> This works just fine, it retrieves records in 2-3 seconds. The problem
> is when I combine more predicates. For example:
> *******
> SELECT TOP 100 *
> FROM SearchCache
> WHERE ((searchType = 2) AND (searchField = 2) AND (value LIKE 'name1%'
> OR value LIKE 'name2%'))
> OR ((searchType = 2) AND (searchField = 3) AND (value LIKE
> 'anothervalue1%' OR value LIKE 'anothervalue2%'))
> *******
> this may take up to 30-40 seconds!
> Any suggestion?
> Thanks in advanced.
>sql

Optimizing table with more than 54 million records

I have a table that has more than 54 million records and I'm searching
records using the LIKE statement. I looking for ways to
optimize/partition/etc. this table.
This is the table structure:
TABLE "SEARCHCACHE"
Fields:
- searchType int
- searchField int
- value varchar(500)
- externalKey int
For example, a simple search would be:
*******
SELECT TOP 100 *
FROM SearchCache
WHERE (searchType = 2) AND (searchField = 2) AND (value LIKE 'name1%' OR
value LIKE 'name2%')
*******
This works just fine, it retrieves records in 2-3 seconds. The problem
is when I combine more predicates. For example:
*******
SELECT TOP 100 *
FROM SearchCache
WHERE ((searchType = 2) AND (searchField = 2) AND (value LIKE 'name1%'
OR value LIKE 'name2%'))
OR ((searchType = 2) AND (searchField = 3) AND (value LIKE
'anothervalue1%' OR value LIKE 'anothervalue2%'))
*******
this may take up to 30-40 seconds!
Any suggestion?
Thanks in advanced.On Jul 30, 1:23 pm, Gaspar <gas...@.no-reply.com> wrote:
> I have a table that has more than 54 million records and I'm searching
> records using the LIKE statement. I looking for ways to
> optimize/partition/etc. this table.
> This is the table structure:
> TABLE "SEARCHCACHE"
> Fields:
> - searchType int
> - searchField int
> - value varchar(500)
> - externalKey int
> For example, a simple search would be:
> *******
> SELECT TOP 100 *
> FROM SearchCache
> WHERE (searchType = 2) AND (searchField = 2) AND (value LIKE 'name1%' OR
> value LIKE 'name2%')
> *******
> This works just fine, it retrieves records in 2-3 seconds. The problem
> is when I combine more predicates. For example:
> *******
> SELECT TOP 100 *
> FROM SearchCache
> WHERE ((searchType = 2) AND (searchField = 2) AND (value LIKE 'name1%'
> OR value LIKE 'name2%'))
> OR ((searchType = 2) AND (searchField = 3) AND (value LIKE
> 'anothervalue1%' OR value LIKE 'anothervalue2%'))
> *******
> this may take up to 30-40 seconds!
> Any suggestion?
> Thanks in advanced.
maybe you could consider full-text search on the table? this would
require afair rewriting queries though.|||The original solution used FullText but it didn't work for my scenario.
That's why I implemented this SearchCache table.
Thanks.
Piotr Rodak wrote:
> On Jul 30, 1:23 pm, Gaspar <gas...@.no-reply.com> wrote:
> maybe you could consider full-text search on the table? this would
> require afair rewriting queries though.
>|||Gaspar
1) Don't use TOP 100 command especially without ORDER BY clause
2) Please provide some details from an execution plan
"Gaspar" <gaspar@.no-reply.com> wrote in message
news:e1xYASq0HHA.4824@.TK2MSFTNGP02.phx.gbl...
>I have a table that has more than 54 million records and I'm searching
> records using the LIKE statement. I looking for ways to
> optimize/partition/etc. this table.
> This is the table structure:
> TABLE "SEARCHCACHE"
> Fields:
> - searchType int
> - searchField int
> - value varchar(500)
> - externalKey int
> For example, a simple search would be:
> *******
> SELECT TOP 100 *
> FROM SearchCache
> WHERE (searchType = 2) AND (searchField = 2) AND (value LIKE 'name1%' OR
> value LIKE 'name2%')
> *******
> This works just fine, it retrieves records in 2-3 seconds. The problem
> is when I combine more predicates. For example:
> *******
> SELECT TOP 100 *
> FROM SearchCache
> WHERE ((searchType = 2) AND (searchField = 2) AND (value LIKE 'name1%'
> OR value LIKE 'name2%'))
> OR ((searchType = 2) AND (searchField = 3) AND (value LIKE
> 'anothervalue1%' OR value LIKE 'anothervalue2%'))
> *******
> this may take up to 30-40 seconds!
> Any suggestion?
> Thanks in advanced.|||Gaspar,
Try unioning the result from independent statements.
select top 100 *
from
(
SELECT TOP 100 *
FROM SearchCache
WHERE ((searchType = 2) AND (searchField = 2) AND (value LIKE 'name1%'
OR value LIKE 'name2%'))
union
SELECT TOP 100 *
FROM SearchCache
where ((searchType = 2) AND (searchField = 3) AND (value LIKE
'anothervalue1%' OR value LIKE 'anothervalue2%'))
) as t
AMB
"Gaspar" wrote:

> I have a table that has more than 54 million records and I'm searching
> records using the LIKE statement. I looking for ways to
> optimize/partition/etc. this table.
> This is the table structure:
> TABLE "SEARCHCACHE"
> Fields:
> - searchType int
> - searchField int
> - value varchar(500)
> - externalKey int
> For example, a simple search would be:
> *******
> SELECT TOP 100 *
> FROM SearchCache
> WHERE (searchType = 2) AND (searchField = 2) AND (value LIKE 'name1%' OR
> value LIKE 'name2%')
> *******
> This works just fine, it retrieves records in 2-3 seconds. The problem
> is when I combine more predicates. For example:
> *******
> SELECT TOP 100 *
> FROM SearchCache
> WHERE ((searchType = 2) AND (searchField = 2) AND (value LIKE 'name1%'
> OR value LIKE 'name2%'))
> OR ((searchType = 2) AND (searchField = 3) AND (value LIKE
> 'anothervalue1%' OR value LIKE 'anothervalue2%'))
> *******
> this may take up to 30-40 seconds!
> Any suggestion?
> Thanks in advanced.
>|||You may want to try running the profiler and check suggestions under the
tuning wizard for the tables - it could be a lack of a good index.
--
Regards,
Jamie
"Gaspar" wrote:

> I have a table that has more than 54 million records and I'm searching
> records using the LIKE statement. I looking for ways to
> optimize/partition/etc. this table.
> This is the table structure:
> TABLE "SEARCHCACHE"
> Fields:
> - searchType int
> - searchField int
> - value varchar(500)
> - externalKey int
> For example, a simple search would be:
> *******
> SELECT TOP 100 *
> FROM SearchCache
> WHERE (searchType = 2) AND (searchField = 2) AND (value LIKE 'name1%' OR
> value LIKE 'name2%')
> *******
> This works just fine, it retrieves records in 2-3 seconds. The problem
> is when I combine more predicates. For example:
> *******
> SELECT TOP 100 *
> FROM SearchCache
> WHERE ((searchType = 2) AND (searchField = 2) AND (value LIKE 'name1%'
> OR value LIKE 'name2%'))
> OR ((searchType = 2) AND (searchField = 3) AND (value LIKE
> 'anothervalue1%' OR value LIKE 'anothervalue2%'))
> *******
> this may take up to 30-40 seconds!
> Any suggestion?
> Thanks in advanced.
>

Optimizing table in mssql

Hi there,
Is there an optimize table command in mssql which will work the same way as "OPTIMIZE TABLE tablename" of mysql?

I have a php application that should work on both mysql and mssql. To do defragmentation, I am using the above command. Is there an equivalent in mssql?

Cheers,
CeliaI presume optimise table defragments the table and indexes? If not - please post what it means.

To defrag an index check out the following in BoL:
2000
- DBCC DBREINDEX
- DBCC INDEXDEFRAG
2005
- ALTER INDEX ... REORGANISE\ REBUILD

You will need to see which options are best for your environment.

Optimizing SQL Query performance

Hi,
I have collected SQL Trace events into a trace table by exporting the trace
into a table.
I have a table Trace1 with following columns:
-RowNumber
-ApplicationName
-DatabaseName
.
.
.
-StartTime
-EndTime
Clustered Index on RowNumber Column.
NonClustered Index on StartTime Column.
Trace1 table contains 100,000 of rows.
Now I am firing a query something like
"select * from Trace1 where StartTime > 'Date1' and StartTime < 'Date2'"
But above query gives me timeout error for most of the cases. I have
specified timeout value as 60 seconds.
How can I solve this timeout issue?
Do I need to have some other proper indexes, if current indexes are not
proper?
Do I need to increase the timeout value? What is the optimum value of
Timeout in such scenarios? I am expecting that this table is going to grow
to contain atleast 5 crores of rows. So please suggest what strategy should
I adopt?
Thanks,
PushkarIf you have an index on StartTime, this should be the most efficient way to
retrieve the data. I would not expect 100,000 rows to take more than a
moment. Even without an index, I would expect the query to finish in
seconds.
Check your execution plan and see if it is using the index.
You could try regenerating your statistics on this table, which should get
it to use this index.
Post the full DDL of your table including the indexes themselves, just so we
are perfectly clear on what you have.
http://www.aspfaq.com/etiquette.asp?id=5006
I think the likely culprit here is the use of "Select * ". If this is a
very wide table, you may be timing out moving all of that data across the
network. Also, how many rows does your typical date range select? If you
usually return 90,000 rows, that makes a big difference. If every row
contains 1 meg of data (an extreme case, just to illustrate a point), for
example, that would be 90 gigs moving over the network, and would timeout
every time.
Also, what application are you using to run the query?
"Pushkar" <pushkartiwari@.gmail.com> wrote in message
news:%23aMdJOHaGHA.1192@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have collected SQL Trace events into a trace table by exporting the
trace
> into a table.
> I have a table Trace1 with following columns:
> -RowNumber
> -ApplicationName
> -DatabaseName
> .
> .
> .
> -StartTime
> -EndTime
> Clustered Index on RowNumber Column.
> NonClustered Index on StartTime Column.
> Trace1 table contains 100,000 of rows.
> Now I am firing a query something like
> "select * from Trace1 where StartTime > 'Date1' and StartTime < 'Date2'"
> But above query gives me timeout error for most of the cases. I have
> specified timeout value as 60 seconds.
> How can I solve this timeout issue?
> Do I need to have some other proper indexes, if current indexes are not
> proper?
> Do I need to increase the timeout value? What is the optimum value of
> Timeout in such scenarios? I am expecting that this table is going to grow
> to contain atleast 5 crores of rows. So please suggest what strategy
should
> I adopt?
> Thanks,
> Pushkar
>
>
>|||Pushkar,
It depends on the relative amount of rows that the query returns.
I would start with adding a (nonclustered) index on StartTime. If the
query returns just a few percent of all rows and tables rows are
relatively wide as compared to the StartTime column, then this index
will probably be used.
If the query returns more than a few percent, or the rows are narrow,
then a nonclustered index on StartTime might be ignored. If this query
is one of the most important queries in your system, then you could
create a clustered index on StartTime (you will need to change the
current clustered index to nonclustered).
HTH,
Gert-Jan
Pushkar wrote:
> Hi,
> I have collected SQL Trace events into a trace table by exporting the trac
e
> into a table.
> I have a table Trace1 with following columns:
> -RowNumber
> -ApplicationName
> -DatabaseName
> .
> .
> .
> -StartTime
> -EndTime
> Clustered Index on RowNumber Column.
> NonClustered Index on StartTime Column.
> Trace1 table contains 100,000 of rows.
> Now I am firing a query something like
> "select * from Trace1 where StartTime > 'Date1' and StartTime < 'Date2'"
> But above query gives me timeout error for most of the cases. I have
> specified timeout value as 60 seconds.
> How can I solve this timeout issue?
> Do I need to have some other proper indexes, if current indexes are not
> proper?
> Do I need to increase the timeout value? What is the optimum value of
> Timeout in such scenarios? I am expecting that this table is going to grow
> to contain atleast 5 crores of rows. So please suggest what strategy shoul
d
> I adopt?
> Thanks,
> Pushkar

Optimizing SQL - Union

Hello all,

I have a table with thousands of rows and is in this format:

id col1 col2 col3 col4
-- -- -- -- --
1 nm 78 xyz pir
2 bn 45 abc dir

I now want to get the data from this table in this format:

field val
--------
col1 nm
col1 bn
col2 78
col2 45
col3 xyz
col3 abc
col4 pir
col4 dir

In order to do this I am doing a union:

select * into #tempUpdate
(
select 'col1' as field, col1 as val from table1
union
select 'col2' as field, col2 as val from table1
union
select 'col3' as field, col3 as val from table1
)

the above example query is smaller - I have a much bigger table with
about 80 columns (Imagine the size of my union query :) and this takes
a lot of time to execute. Can someone please suggest a better way to do
this?

The results of this union query are selected into a temp table, which I
then use to update another table. I am using SQL Server 2000.

my main concern is performance. any ideas please?

thanksIf you have SQL 2005, you can use UNPIVOT. If you are using earlier
releases, try:

select
m.id
, x.col
, case x.col
when 1 then m.col1
when 2 then m.col2
when 3 then m.col3
when 4 then m.col4
end as val
from
MyTable m
cross join
(
select 'col1' union all
select 'col1' union all
select 'col1' union all
select 'col4'
) as x (col)

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"das" <Adityanad@.gmail.com> wrote in message
news:1143651738.146958.160520@.t31g2000cwb.googlegr oups.com...
Hello all,

I have a table with thousands of rows and is in this format:

id col1 col2 col3 col4
-- -- -- -- --
1 nm 78 xyz pir
2 bn 45 abc dir

I now want to get the data from this table in this format:

field val
--------
col1 nm
col1 bn
col2 78
col2 45
col3 xyz
col3 abc
col4 pir
col4 dir

In order to do this I am doing a union:

select * into #tempUpdate
(
select 'col1' as field, col1 as val from table1
union
select 'col2' as field, col2 as val from table1
union
select 'col3' as field, col3 as val from table1
)

The results of this union query are selected into a temp table, which I
then use to update another table. I am using SQL Server 2000.

my main concern is performance. any ideas please?

thanks|||Ok, I will try this. I am a liitle bit confused about the cross join..

I will test and let you know. Thanks!|||On 29 Mar 2006 09:02:18 -0800, das wrote:

(snip)
>In order to do this I am doing a union:
>select * into #tempUpdate
>(
> select 'col1' as field, col1 as val from table1
> union
> select 'col2' as field, col2 as val from table1
> union
> select 'col3' as field, col3 as val from table1
>)
>the above example query is smaller - I have a much bigger table with
>about 80 columns (Imagine the size of my union query :) and this takes
>a lot of time to execute. Can someone please suggest a better way to do
>this?

Hi das,

Somewhat simpler than the suggestions Tom posted (and probably less
efficient, but still a major win over your present version) is the
following simple change:

select 'col1' as field, col1 as val from table1
union ALL
select 'col2' as field, col2 as val from table1
union ALL
select 'col3' as field, col3 as val from table1

UNION without ALL will attempt to remove duplicates; with large result
sets, checking for duplicates can be a major performance killer. With
UNION ALL, you say "don't attempt to remove duplicates" - either because
you want them or (in this case) because you're sure there aren't any.

--
Hugo Kornelis, SQL Server MVP|||that's a really good advice, didn't know what 'union all' meant all
these days.
I tried Thomas approach and it is much faster than before.
thanks a lot guys.|||Been away for a while. Here's a correction:

select
m.id
, x.col
, case x.col
when 1 then m.col1
when 2 then m.col2
when 3 then m.col3
when 4 then m.col4
end as val
from
MyTable m
cross join
(
select 'col1' union all
select 'col2' union all
select 'col3' union all
select 'col4'
) as x (col)

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com

"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:8izWf.1879$m35.157124@.news20.bellglobal.com.. .
If you have SQL 2005, you can use UNPIVOT. If you are using earlier
releases, try:

--
Tom

I have a table with thousands of rows and is in this format:

id col1 col2 col3 col4
-- -- -- -- --
1 nm 78 xyz pir
2 bn 45 abc dir

I now want to get the data from this table in this format:

field val
--------
col1 nm
col1 bn
col2 78
col2 45
col3 xyz
col3 abc
col4 pir
col4 dir

In order to do this I am doing a union:

select * into #tempUpdate
(
select 'col1' as field, col1 as val from table1
union
select 'col2' as field, col2 as val from table1
union
select 'col3' as field, col3 as val from table1
)

The results of this union query are selected into a temp table, which I
then use to update another table. I am using SQL Server 2000.

my main concern is performance. any ideas please?

thanks|||THANKS THOMAS!sql

Optimizing SELECT query

I have one table (tableDemo) with following structure:

TSID bigint (Primary Key)
TskID bigint
Sequence bigint
Version bigint
Frequency varchar(500)
WOID bigint
DateSchedule datetime
TimeStandard real
MeterEstimated real
MeterLast real
Description ntext
CreatedBy bigint
CreatedDate datetime
ModifiedBy bigint
ModifiedDate datetime
Id uniqueidentifier

I have 8000 records in this table. When I fire simple select command (i.e. select * from tableDemo) it takes more than 120 seconds.

Is there any techniques where I can access all these records within 2-3 seconds ?

Regards,

Why do you need every field of every record? Surely you are not going to display them all on one page? If you want to use them for paging, and are using Sql Server 2005, have a look at ROW_NUMBER:

http://www.davidhayden.com/blog/dave/archive/2005/12/30/2652.aspx
http://msdn2.microsoft.com/en-us/library/ms186734.aspx

|||

Are there any large files stored in any of the rows being returned? i.e. files or larges amounts of text? Returning only 8000 rows shouldnt take very long esp not 120 seconds. Is the instance of sql on your local machine or located someone else?

Tim

Optimizing query with UDF and table vars and IN

Hi, I am trying to optimize this scenario.
I have a query that is returning a list of services. Each service is done
by an employee and for a client. Employees have rights to see only certain
sevices. They can see any service that is done by an employee they have
rights to OR done for a client they have rights to.
CREATE TABLE [Service] (
service_id Int IDENTITY(1,1) NOT NULL,
emp_id Int,
client_id Int)
I have 2 UDFs that return a list of emp_id's they have rights to and a list
of client_id's they have rights to respectively.
CREATE FUNCTION dbo.f_list_emps (@.my_emp_id Int)
RETURNS @.EmpList TABLE (emp_id int not null unique)
AS
BEGIN
// Fill @.EmpList here with a bunch of queries
END
CREATE FUNCTION dbo.f_list_clients (@.my_emp_id Int)
RETURNS @.ClientList TABLE (client_id int not null unique)
AS
BEGIN
// Fill @.ClientList here with a bunch of queries
END
The actual query is built dynamically because it can have about 15 different
parameters passed to it, but a simplified version would look like:
SELECT * FROM Service
WHERE emp_id IN
(SELECT emp_id FROM dbo.f_list_emps(@.my_emp_id))
OR client_id IN
(SELECT client_id FROM dbo.f_list_clients(@.my_emp_id))
I'm looking for a way to optimize this a bit better. I can't join the table
vars directly because of the 'OR', and I don't want to do a UNION of 2
queries each with a separate join because of all the other parameters
involved in the query.
Thanks for any advice,
DaveDavid D Webb (spivey@.nospam.post.com) writes:
> The actual query is built dynamically because it can have about 15
> different parameters passed to it, but a simplified version would look
> like: >
> SELECT * FROM Service
> WHERE emp_id IN
> (SELECT emp_id FROM dbo.f_list_emps(@.my_emp_id))
> OR client_id IN
> (SELECT client_id FROM dbo.f_list_clients(@.my_emp_id))
> I'm looking for a way to optimize this a bit better. I can't join the
> table vars directly because of the 'OR', and I don't want to do a UNION
> of 2 queries each with a separate join because of all the other
> parameters involved in the query.
It's of course impossible to suggest optimizations when I don't see
the tables, and do not the full query.
What I would consider is to insert the data from the table functions
into temp tables. Temp tables have statistics, and since you are running
a dynamic query anyway, you could just as well make use of that statistics.
If you use the UDFs in the query, SQL Server will make some standard
assumptions about what they return.
I would also consider running a UNION of two queries. If you are building
the query dynamically, it should not be much of an issue to repeat the
queries. But you should benchmark whether UNION actually gives an
improvement.
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|||A simplified query probably won't do.
But here a tip that might be useful: drop the UDF's. If you can rewrite
them as views, then the optimizer can properly optimize the query. When
using UDF's in this fashion you are bound to run into performance
problems as the resultset grows.
Gert-Jan
David D Webb wrote:
> Hi, I am trying to optimize this scenario.
> I have a query that is returning a list of services. Each service is done
> by an employee and for a client. Employees have rights to see only certai
n
> sevices. They can see any service that is done by an employee they have
> rights to OR done for a client they have rights to.
> CREATE TABLE [Service] (
> service_id Int IDENTITY(1,1) NOT NULL,
> emp_id Int,
> client_id Int)
> I have 2 UDFs that return a list of emp_id's they have rights to and a lis
t
> of client_id's they have rights to respectively.
> CREATE FUNCTION dbo.f_list_emps (@.my_emp_id Int)
> RETURNS @.EmpList TABLE (emp_id int not null unique)
> AS
> BEGIN
> // Fill @.EmpList here with a bunch of queries
> END
> CREATE FUNCTION dbo.f_list_clients (@.my_emp_id Int)
> RETURNS @.ClientList TABLE (client_id int not null unique)
> AS
> BEGIN
> // Fill @.ClientList here with a bunch of queries
> END
> The actual query is built dynamically because it can have about 15 differe
nt
> parameters passed to it, but a simplified version would look like:
> SELECT * FROM Service
> WHERE emp_id IN
> (SELECT emp_id FROM dbo.f_list_emps(@.my_emp_id))
> OR client_id IN
> (SELECT client_id FROM dbo.f_list_clients(@.my_emp_id))
> I'm looking for a way to optimize this a bit better. I can't join the tab
le
> vars directly because of the 'OR', and I don't want to do a UNION of 2
> queries each with a separate join because of all the other parameters
> involved in the query.
> Thanks for any advice,
> Dave

Optimizing queries

friends,
I have the following table with atleast 4,00,000+ records & i want to
update some of its fields with the below logic.
STOCKEXCDOWNLOAD
I've ran the below query in query analyzer with no indexes on this
table. the time taken is about 40 secs.
UPDATE StockExcDownload SET CalCheqAmt = Quantity * (CASE WHEN Price =
9999.00
THEN (SELECT MktPrice FROM Issue) ELSE Price END), BSENSEFlag = 'B',
EditDate = getdate()
But the index tuning wizard has suggested the following index to be
created.
CREATE
INDEX [StockExcDownload1] ON [dbo].[StockExcDownload] ([Price],
[Quantity])
WITH
DROP_EXISTING
ON [PRIMARY]
But after implementing, the above index it the query has taken me
about 7 mins to execute.
what is the reason for this?rameshsaive@.gmail.com wrote:
> friends,
> I have the following table with atleast 4,00,000+ records & i want to
> update some of its fields with the below logic.
>
> STOCKEXCDOWNLOAD
> I've ran the below query in query analyzer with no indexes on this
> table. the time taken is about 40 secs.
> UPDATE StockExcDownload SET CalCheqAmt = Quantity * (CASE WHEN Price =
> 9999.00
> THEN (SELECT MktPrice FROM Issue) ELSE Price END), BSENSEFlag = 'B',
> EditDate = getdate()
> But the index tuning wizard has suggested the following index to be
> created.
> CREATE
> INDEX [StockExcDownload1] ON [dbo].[StockExcDownload] ([Price],
> [Quantity])
> WITH
> DROP_EXISTING
> ON [PRIMARY]
>
> But after implementing, the above index it the query has taken me
> about 7 mins to execute.
> what is the reason for this?
The subquery:
(SELECT MktPrice FROM Issue)
is invalid unless Issue contains no more than ONE row. I suspect this
may be part of the problem but without more info I can only guess what
the solution is. Read my signature.
Why do you want to calculate an amount on the table if it can already
be derived from other tables in the database? Don't store calculated
results if you can avoid it. Put the calcs in a view or query.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks a lot david.
At any time the issue table contains only one record.
i've modified the query to
UPDATE StockExcDownload SET CalCheqAmt = Quantity * (CASE WHEN Price =
9999.00 THEN 34 ELSE Price END), BSENSEFlag = 'B',
EditDate = getdate()
It has boosted a little bit (its coming down to 4.45 Mins). But i still
want to reduce the time as it is too costly for me to use this query.
And also it is one of the queries in the stored procedure. below is
the list of queries preceding above query. I've tried the above query
for indexing.
UPDATE StockExcDownload SET CatgNm = (SELECT DISTINCT TOP 1 CAT.CatgNm
FROM Category CAT, InvType INV
WHERE CAT.CatgNm = 'EMP' AND CAT.CatgCD = INV.CatgCD AND INV.ITCD =
StockExcDownload.InvstNm AND StockExcDownload.InvstNm = 'EMP'
ORDER BY CAT.CatgNm) WHERE StockExcDownload.CatgNm IS NULL AND
StockExcDownload.BSENSEFlag = 'B'
UPDATE StockExcDownload SET CatgNm = 'RETAIL' WHERE CalCheqAmt <=
100000 AND StockExcDownload.BSENSEFlag = 'B'
AND StockExcDownload.CatgNm IS NULL
UPDATE StockExcDownload SET CatgNm = (SELECT DISTINCT TOP 1 CAT.CatgNm
FROM Category CAT, InvType INV
WHERE StockExcDownload.CalCheqAmt BETWEEN CAT.MinAmt AND CAT.MaxAmt AND
CAT.CatgCD = INV.CatgCD
AND INV.ITCD = StockExcDownload.InvstNm ORDER BY CAT.CatgNm)
WHERE StockExcDownload.CatgNm IS NULL AND StockExcDownload.BSENSEFlag =
'B'
UPDATE StockExcDownload SET Catgnm = (SELECT DISTINCT TOP 1 CAT.CatgNm
FROM Category CAT
WHERE StockExcDownload.CalCheqAmt BETWEEN CAT.MinAmt AND CAT.MaxAmt
ORDER BY CAT.CatgNm)
WHERE StockExcDownload.CatgNm IS NULL AND StockExcDownload.BSENSEFlag =
'B'
UPDATE StockExcDownload SET CatgNm = 'RETAIL' WHERE CatgNm IS NULL AND
BSENSEFlag = 'B'
UPDATE StockExcDownload SET SyndNm = T.SyndNm, Name = T.BrokerNm,
CityNm = T.CityNm
FROM Terminal T WHERE T.BrokerID = StockExcDownload.UserCD AND
T.TerminalCD = StockExcDownload.BranchCD
AND StockExcDownload.BSENSEFlag = 'B'
UPDATE StockExcDownload SET BrokerFlag = LEFT(CompNm,1) FROM Company
A, Broker B
WHERE B.BrokerID = StockExcDownload.UserCD AND B.DSPML = 1 AND
StockExcDownload.BSENSEFlag = 'B'
UPDATE StockExcDownload SET CleanDirty = 'C' WHERE OrderNo IN(SELECT
TOP 1 OrderNo FROM StockExcDownload STI
WHERE STI.ApplNo1 = StockExcDownload.ApplNo1
ORDER BY STI.ApplNo1 ASC,STI.CalCheqAmt DESC,STI.Quantity DESC)
UPDATE StockExcDownload SET CleanDirty = 'D' WHERE CleanDirty IS NULL
UPDATE StockExcDownload SET SyndNm = (Case WHEN BSENSEFlag = 'B' THEN
UserCD ELSE BrokerCD END) WHERE SyndNm IS NULL
--UPDATE StockExcDownload SET SyndNm = BrokerCD WHERE SyndNm IS NULL
AND BSENSEFlag = 'N'
--UPDATE StockExcDownload SET SyndNm = UserCD WHERE SyndNm IS NULL AND
BSENSEFlag = 'B'
UPDATE StockExcDownload SET Name = BranchCD WHERE Name IS NULL
EXEC ActualCleanBidProcess|||rameshsaive@.gmail.com wrote:
> Thanks a lot david.
> At any time the issue table contains only one record.
> i've modified the query to
> UPDATE StockExcDownload SET CalCheqAmt = Quantity * (CASE WHEN Price =
> 9999.00 THEN 34 ELSE Price END), BSENSEFlag = 'B',
> EditDate = getdate()
> It has boosted a little bit (its coming down to 4.45 Mins). But i still
> want to reduce the time as it is too costly for me to use this query.
> And also it is one of the queries in the stored procedure. below is
> the list of queries preceding above query. I've tried the above query
> for indexing.
> UPDATE StockExcDownload SET CatgNm = (SELECT DISTINCT TOP 1 CAT.CatgNm
> FROM Category CAT, InvType INV
> WHERE CAT.CatgNm = 'EMP' AND CAT.CatgCD = INV.CatgCD AND INV.ITCD =
> StockExcDownload.InvstNm AND StockExcDownload.InvstNm = 'EMP'
> ORDER BY CAT.CatgNm) WHERE StockExcDownload.CatgNm IS NULL AND
> StockExcDownload.BSENSEFlag = 'B'
> UPDATE StockExcDownload SET CatgNm = 'RETAIL' WHERE CalCheqAmt <=
> 100000 AND StockExcDownload.BSENSEFlag = 'B'
> AND StockExcDownload.CatgNm IS NULL
> UPDATE StockExcDownload SET CatgNm = (SELECT DISTINCT TOP 1 CAT.CatgNm
> FROM Category CAT, InvType INV
> WHERE StockExcDownload.CalCheqAmt BETWEEN CAT.MinAmt AND CAT.MaxAmt AND
> CAT.CatgCD = INV.CatgCD
> AND INV.ITCD = StockExcDownload.InvstNm ORDER BY CAT.CatgNm)
> WHERE StockExcDownload.CatgNm IS NULL AND StockExcDownload.BSENSEFlag =
> 'B'
> UPDATE StockExcDownload SET Catgnm = (SELECT DISTINCT TOP 1 CAT.CatgNm
> FROM Category CAT
> WHERE StockExcDownload.CalCheqAmt BETWEEN CAT.MinAmt AND CAT.MaxAmt
> ORDER BY CAT.CatgNm)
> WHERE StockExcDownload.CatgNm IS NULL AND StockExcDownload.BSENSEFlag =
> 'B'
> UPDATE StockExcDownload SET CatgNm = 'RETAIL' WHERE CatgNm IS NULL AND
> BSENSEFlag = 'B'
> UPDATE StockExcDownload SET SyndNm = T.SyndNm, Name = T.BrokerNm,
> CityNm = T.CityNm
> FROM Terminal T WHERE T.BrokerID = StockExcDownload.UserCD AND
> T.TerminalCD = StockExcDownload.BranchCD
> AND StockExcDownload.BSENSEFlag = 'B'
> UPDATE StockExcDownload SET BrokerFlag = LEFT(CompNm,1) FROM Company
> A, Broker B
> WHERE B.BrokerID = StockExcDownload.UserCD AND B.DSPML = 1 AND
> StockExcDownload.BSENSEFlag = 'B'
> UPDATE StockExcDownload SET CleanDirty = 'C' WHERE OrderNo IN(SELECT
> TOP 1 OrderNo FROM StockExcDownload STI
> WHERE STI.ApplNo1 = StockExcDownload.ApplNo1
> ORDER BY STI.ApplNo1 ASC,STI.CalCheqAmt DESC,STI.Quantity DESC)
> UPDATE StockExcDownload SET CleanDirty = 'D' WHERE CleanDirty IS NULL
> UPDATE StockExcDownload SET SyndNm = (Case WHEN BSENSEFlag = 'B' THEN
> UserCD ELSE BrokerCD END) WHERE SyndNm IS NULL
> --UPDATE StockExcDownload SET SyndNm = BrokerCD WHERE SyndNm IS NULL
> AND BSENSEFlag = 'N'
> --UPDATE StockExcDownload SET SyndNm = UserCD WHERE SyndNm IS NULL AND
> BSENSEFlag = 'B'
> UPDATE StockExcDownload SET Name = BranchCD WHERE Name IS NULL
> EXEC ActualCleanBidProcess
Indexes on StockExcDownload aren't going to help because all these
updates will perform a scan of the entire table/clustered index anyway.
Make sure you have indexes in the other tables on the columns used in
your joins.
Combine as many of the UPDATEs as you can. Using joins rather than
subqueries in the UPDATE statements may help. IMPORTANT: Make sure you
only join on keys that are unique in the table you are making the
update from.
Of those UPDATEs, seven of them don't join to any other table. Going by
the table name it looks like this is a "staging" table for
pre-processing before you move the data elsewhere. If that's the case
then you could eliminate those 7 UPDATEs altogether. Do the work in the
INSERT statement when you load to the production tables. Even if that
isn't possible, you should be able to combine those 7 UPDATEs into one,
which will be a very significant improvement.
Where you have a very large update against data that is otherwise
static, it will often help to batch the UPDATE into smaller
transactions. For example:
SET ROWCOUNT 100000
WHILE 1=1
BEGIN
UPDATE StockExcDownload
SET y = T.y
FROM tbl AS T
WHERE StockExcDownload.x = T.x
AND StockExcDownload.y IS NULL ;
IF @.@.ROWCOUNT=0
BREAK
END
SET ROWCOUNT 0
Experiment with the SET ROWCOUNT option to see what size of batch works
best for you.
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Based on the UPDATE statement in the original post, the advice to add an
index does not make sense, because there is no WHERE clause in this
query, so no index will speed up the data retrieval.
Looking at the other UPDATE queries, a clustered index on (BSENSEFlag)
might increase performance.
For futher comments/suggestions, see inline.
"rameshsaive@.gmail.com" wrote:
> Thanks a lot david.
> At any time the issue table contains only one record.
> i've modified the query to
> UPDATE StockExcDownload SET CalCheqAmt = Quantity * (CASE WHEN Price =
> 9999.00 THEN 34 ELSE Price END), BSENSEFlag = 'B',
> EditDate = getdate()
If you have chosen to create a clustered index on (BSENSEFlag), then it
might be worth your while to split the above query in the following two:
UPDATE StockExcDownload
SET CalCheqAmt = Quantity * (CASE WHEN Price = 9999.00 THEN 34 ELSE
Price END)
, BSENSEFlag = 'B'
, EditDate = getdate()
WHERE BSENSEFlag <> 'B'
UPDATE StockExcDownload
SET CalCheqAmt = Quantity * (CASE WHEN Price = 9999.00 THEN 34 ELSE
Price END)
, EditDate = getdate()
WHERE BSENSEFlag = 'B'

> It has boosted a little bit (its coming down to 4.45 Mins). But i still
> want to reduce the time as it is too costly for me to use this query.
> And also it is one of the queries in the stored procedure. below is
> the list of queries preceding above query. I've tried the above query
> for indexing.
> UPDATE StockExcDownload SET CatgNm = (SELECT DISTINCT TOP 1 CAT.CatgNm
> FROM Category CAT, InvType INV
> WHERE CAT.CatgNm = 'EMP' AND CAT.CatgCD = INV.CatgCD AND INV.ITCD =
> StockExcDownload.InvstNm AND StockExcDownload.InvstNm = 'EMP'
> ORDER BY CAT.CatgNm) WHERE StockExcDownload.CatgNm IS NULL AND
> StockExcDownload.BSENSEFlag = 'B'
Is there some kind of generator that created this query? Why not write
something like this:
UPDATE StockExcDownload
SET CatgNm = 'EMP'
WHERE StockExcDownload.CatgNm IS NULL
AND StockExcDownload.BSENSEFlag = 'B'
AND StockExcDownload.InvstNm = 'EMP'
AND EXISTS (
SELECT 1
FROM Category CAT
INNER JOIN InvType INV
ON INV.CatgCD = CAT.CatgCD
WHERE CAT.CatgNm = 'EMP'
AND INV.ITCD = StockExcDownload.InvstNm
)
There is one slight difference with your original: it will not set
CatgNm to NULL when there is no match. But since you are only updating
rows where this column is already NULL, this is actually a good thing.

> UPDATE StockExcDownload SET CatgNm = 'RETAIL' WHERE CalCheqAmt <=
> 100000 AND StockExcDownload.BSENSEFlag = 'B'
> AND StockExcDownload.CatgNm IS NULL
> UPDATE StockExcDownload SET CatgNm = (SELECT DISTINCT TOP 1 CAT.CatgNm
> FROM Category CAT, InvType INV
> WHERE StockExcDownload.CalCheqAmt BETWEEN CAT.MinAmt AND CAT.MaxAmt AND
> CAT.CatgCD = INV.CatgCD
> AND INV.ITCD = StockExcDownload.InvstNm ORDER BY CAT.CatgNm)
> WHERE StockExcDownload.CatgNm IS NULL AND StockExcDownload.BSENSEFlag =
> 'B'
The ORDER BY clause in the subquery matches the Selection List. So you
don't need to use TOP, and when you use TOP 1, there is definitely no
use for the DISTINCT keyword. You could write SELECT MIN(..) instead of
SELECT DISTINCT TOP 1 .. ORDER BY ..

> UPDATE StockExcDownload SET Catgnm = (SELECT DISTINCT TOP 1 CAT.CatgNm
> FROM Category CAT
> WHERE StockExcDownload.CalCheqAmt BETWEEN CAT.MinAmt AND CAT.MaxAmt
> ORDER BY CAT.CatgNm)
> WHERE StockExcDownload.CatgNm IS NULL AND StockExcDownload.BSENSEFlag =
> 'B'
> UPDATE StockExcDownload SET CatgNm = 'RETAIL' WHERE CatgNm IS NULL AND
> BSENSEFlag = 'B'
This query can be combined with one of the later UPDATEs. It just needs
an UPDATE that will cover the entire table, and a CASE expression to
retain non-NULL CatgNm values.

> UPDATE StockExcDownload SET SyndNm = T.SyndNm, Name = T.BrokerNm,
> CityNm = T.CityNm
> FROM Terminal T WHERE T.BrokerID = StockExcDownload.UserCD AND
> T.TerminalCD = StockExcDownload.BranchCD
> AND StockExcDownload.BSENSEFlag = 'B'
> UPDATE StockExcDownload SET BrokerFlag = LEFT(CompNm,1) FROM Company
> A, Broker B
> WHERE B.BrokerID = StockExcDownload.UserCD AND B.DSPML = 1 AND
> StockExcDownload.BSENSEFlag = 'B'
Ouch! This is no good. The table Company is not joined to Broker or
StockExcDownload, making it a cross join, which could be very expensive!
Also, if the column CompNm originates from table Company, then the
BrokerFlag could be set to any random Company first letter.

> UPDATE StockExcDownload SET CleanDirty = 'C' WHERE OrderNo IN(SELECT
> TOP 1 OrderNo FROM StockExcDownload STI
> WHERE STI.ApplNo1 = StockExcDownload.ApplNo1
> ORDER BY STI.ApplNo1 ASC,STI.CalCheqAmt DESC,STI.Quantity DESC)
I would change "IN" to "=".
Please note, that if there are multiple OrderNo for a particular
combination of (ApplNol, CalCheqAmt, Quantity), then the query engine
will select one of these OrderNo 'randomly'. If you want consistent
results, you might want to add OrderNo to the end of the ORDER BY
clause.

> UPDATE StockExcDownload SET CleanDirty = 'D' WHERE CleanDirty IS NULL
If CleanDirty is NULL for all rows, prior to the previous query, then
you can merge these two queries to something like this:
UPDATE StockExcDownload
SET CleanDirty = CASE WHEN OrderNo = (
SELECT ... ) THEN 'C' ELSE 'D' END
You could also last "CatgNm = 'RETAIL'" query to this one, which would
make it something like
UPDATE StockExcDownload
SET CleanDirty = CASE WHEN OrderNo = (
SELECT ... ) THEN 'C' ELSE 'D' END
, CatgNm = CASE WHEN (CatgNm IS NULL AND BSENSEFlag = 'B') THEN
'RETAIL' ELSE CatgNm END

> UPDATE StockExcDownload SET SyndNm = (Case WHEN BSENSEFlag = 'B' THEN
> UserCD ELSE BrokerCD END) WHERE SyndNm IS NULL
You can add this one to the previous query as well.

> --UPDATE StockExcDownload SET SyndNm = BrokerCD WHERE SyndNm IS NULL
> AND BSENSEFlag = 'N'
> --UPDATE StockExcDownload SET SyndNm = UserCD WHERE SyndNm IS NULL AND
> BSENSEFlag = 'B'
> UPDATE StockExcDownload SET Name = BranchCD WHERE Name IS NULL
And this one as well.
Hope this helps,
Gert-Jan

> EXEC ActualCleanBidProcess

Optimizing Lookups on a trigger's INSERTED virtual table

Hi!
I have a problem with a stored procedure I am analizing, it is a very
critical piece of a system I am developing, this stored procedure should be
the only point of access to update rows in a table, this is for concurrency
control and transaction level is set to serializable.
Now the main problem I am having right now is that when I analyze the
execution of this piece of code in query analyzer the trigger that verifies
the information being inserted is valid takes 39% of the total execution time
for the stored procedure only to execute an "INSERTED SCAN" on the trigger's
INSERTED table (which should always contain only one row). I added TOP 1 and
WITH(fastfirstrow) trying to optimize this operation but got no decrease in
the relative weight of this statement.
Also when I call this sp 10000 times in my test environment with query
analyzer I may get at least 30 events with duration above 100 and at least 5
with more than 1000.
Any tips, specially with the INSERTED SCAN, I could not find any place in
the internet with a strategy to optimize this operation...
Thanks!!!
Post your trigger code and a CREATE TABLE statement for the table.
You mentioned that INSERTED "should always contain only one row". Set-based
code is usually much more efficient anyway so the number of rows affected
should be irrelevant. Don't assign values to variables in a trigger because
multiple procedural statements in triggers will create a bottleneck. For the
same reason it seems unwise and unecessary to add TOP 1 to trigger code
statements. It will make hard work for the DBA to fix data quality issues or
schema changes if he/she is forced to update only one row at a time.
David Portas
SQL Server MVP
|||Thanks a lot David!
Can you recommend good set-based sql programming tutorials, I have heard a
lot about it but I couldn't find any good source of information.
Thanks again! :D
Ignacio
"David Portas" wrote:

> Post your trigger code and a CREATE TABLE statement for the table.
> You mentioned that INSERTED "should always contain only one row". Set-based
> code is usually much more efficient anyway so the number of rows affected
> should be irrelevant. Don't assign values to variables in a trigger because
> multiple procedural statements in triggers will create a bottleneck. For the
> same reason it seems unwise and unecessary to add TOP 1 to trigger code
> statements. It will make hard work for the DBA to fix data quality issues or
> schema changes if he/she is forced to update only one row at a time.
> --
> David Portas
> SQL Server MVP
> --
>
>

Optimizing Lookups on a trigger's INSERTED virtual table

Hi!
I have a problem with a stored procedure I am analizing, it is a very
critical piece of a system I am developing, this stored procedure should be
the only point of access to update rows in a table, this is for concurrency
control and transaction level is set to serializable.
Now the main problem I am having right now is that when I analyze the
execution of this piece of code in query analyzer the trigger that verifies
the information being inserted is valid takes 39% of the total execution time
for the stored procedure only to execute an "INSERTED SCAN" on the trigger's
INSERTED table (which should always contain only one row). I added TOP 1 and
WITH(fastfirstrow) trying to optimize this operation but got no decrease in
the relative weight of this statement.
Also when I call this sp 10000 times in my test environment with query
analyzer I may get at least 30 events with duration above 100 and at least 5
with more than 1000.
Any tips, specially with the INSERTED SCAN, I could not find any place in
the internet with a strategy to optimize this operation...
Thanks!!!Post your trigger code and a CREATE TABLE statement for the table.
You mentioned that INSERTED "should always contain only one row". Set-based
code is usually much more efficient anyway so the number of rows affected
should be irrelevant. Don't assign values to variables in a trigger because
multiple procedural statements in triggers will create a bottleneck. For the
same reason it seems unwise and unecessary to add TOP 1 to trigger code
statements. It will make hard work for the DBA to fix data quality issues or
schema changes if he/she is forced to update only one row at a time.
--
David Portas
SQL Server MVP
--|||Thanks a lot David!
Can you recommend good set-based sql programming tutorials, I have heard a
lot about it but I couldn't find any good source of information.
Thanks again! :D
Ignacio
"David Portas" wrote:
> Post your trigger code and a CREATE TABLE statement for the table.
> You mentioned that INSERTED "should always contain only one row". Set-based
> code is usually much more efficient anyway so the number of rows affected
> should be irrelevant. Don't assign values to variables in a trigger because
> multiple procedural statements in triggers will create a bottleneck. For the
> same reason it seems unwise and unecessary to add TOP 1 to trigger code
> statements. It will make hard work for the DBA to fix data quality issues or
> schema changes if he/she is forced to update only one row at a time.
> --
> David Portas
> SQL Server MVP
> --
>
>sql

Optimizing Lookups on a trigger's INSERTED virtual table

Hi!
I have a problem with a stored procedure I am analizing, it is a very
critical piece of a system I am developing, this stored procedure should be
the only point of access to update rows in a table, this is for concurrency
control and transaction level is set to serializable.
Now the main problem I am having right now is that when I analyze the
execution of this piece of code in query analyzer the trigger that verifies
the information being inserted is valid takes 39% of the total execution tim
e
for the stored procedure only to execute an "INSERTED SCAN" on the trigger's
INSERTED table (which should always contain only one row). I added TOP 1 and
WITH(fastfirstrow) trying to optimize this operation but got no decrease in
the relative weight of this statement.
Also when I call this sp 10000 times in my test environment with query
analyzer I may get at least 30 events with duration above 100 and at least 5
with more than 1000.
Any tips, specially with the INSERTED SCAN, I could not find any place in
the internet with a strategy to optimize this operation...
Thanks!!!Post your trigger code and a CREATE TABLE statement for the table.
You mentioned that INSERTED "should always contain only one row". Set-based
code is usually much more efficient anyway so the number of rows affected
should be irrelevant. Don't assign values to variables in a trigger because
multiple procedural statements in triggers will create a bottleneck. For the
same reason it seems unwise and unecessary to add TOP 1 to trigger code
statements. It will make hard work for the DBA to fix data quality issues or
schema changes if he/she is forced to update only one row at a time.
David Portas
SQL Server MVP
--|||Thanks a lot David!
Can you recommend good set-based sql programming tutorials, I have heard a
lot about it but I couldn't find any good source of information.
Thanks again! :D
Ignacio
"David Portas" wrote:

> Post your trigger code and a CREATE TABLE statement for the table.
> You mentioned that INSERTED "should always contain only one row". Set-base
d
> code is usually much more efficient anyway so the number of rows affected
> should be irrelevant. Don't assign values to variables in a trigger becaus
e
> multiple procedural statements in triggers will create a bottleneck. For t
he
> same reason it seems unwise and unecessary to add TOP 1 to trigger code
> statements. It will make hard work for the DBA to fix data quality issues
or
> schema changes if he/she is forced to update only one row at a time.
> --
> David Portas
> SQL Server MVP
> --
>
>

optimizing load performance using partitioned tables in 2005

Hi,
Iam curious to know if i can increase my load performance using
partitioned tables. If i create a 4-way partitioned table, can i load
directly into a specific partition, so effectively having 4 parallel loads
into the 4 partitions. (Something i can do in Sybase).
If not what can i do to maximize my load on a partitioned table?
Vivek
This is one option, but does your data align itself with these partitions?
Supposed you partition on last name, are your last names going to be evenly
distributed? Last names are a pretty good choice as the distribution is
somewhat even, however a choice like date is probably not so good if your
data is ordered already.
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
"Vivek" <Vivek@.discussions.microsoft.com> wrote in message
news:6196820D-E5C6-4D83-97F1-0D7087B6AEC6@.microsoft.com...
> Hi,
> Iam curious to know if i can increase my load performance using
> partitioned tables. If i create a 4-way partitioned table, can i load
> directly into a specific partition, so effectively having 4 parallel loads
> into the 4 partitions. (Something i can do in Sybase).
> If not what can i do to maximize my load on a partitioned table?
> Vivek
|||Yes my data does align itself with these partitions. So what i want to know
is how do I load data into a specific partition? (using say BCP or Bulk
Insert) What is the syntax?
"Hilary Cotter" wrote:

> This is one option, but does your data align itself with these partitions?
> Supposed you partition on last name, are your last names going to be evenly
> distributed? Last names are a pretty good choice as the distribution is
> somewhat even, however a choice like date is probably not so good if your
> data is ordered already.
> --
> 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
> "Vivek" <Vivek@.discussions.microsoft.com> wrote in message
> news:6196820D-E5C6-4D83-97F1-0D7087B6AEC6@.microsoft.com...
>
>

optimizing load performance using partitioned tables in 2005

Hi,
Iam curious to know if i can increase my load performance using
partitioned tables. If i create a 4-way partitioned table, can i load
directly into a specific partition, so effectively having 4 parallel loads
into the 4 partitions. (Something i can do in Sybase).
If not what can i do to maximize my load on a partitioned table?
VivekThis is one option, but does your data align itself with these partitions?
Supposed you partition on last name, are your last names going to be evenly
distributed? Last names are a pretty good choice as the distribution is
somewhat even, however a choice like date is probably not so good if your
data is ordered already.
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
"Vivek" <Vivek@.discussions.microsoft.com> wrote in message
news:6196820D-E5C6-4D83-97F1-0D7087B6AEC6@.microsoft.com...
> Hi,
> Iam curious to know if i can increase my load performance using
> partitioned tables. If i create a 4-way partitioned table, can i load
> directly into a specific partition, so effectively having 4 parallel loads
> into the 4 partitions. (Something i can do in Sybase).
> If not what can i do to maximize my load on a partitioned table?
> Vivek|||Yes my data does align itself with these partitions. So what i want to know
is how do I load data into a specific partition? (using say BCP or Bulk
Insert) What is the syntax?
"Hilary Cotter" wrote:

> This is one option, but does your data align itself with these partitions?
> Supposed you partition on last name, are your last names going to be evenl
y
> distributed? Last names are a pretty good choice as the distribution is
> somewhat even, however a choice like date is probably not so good if your
> data is ordered already.
> --
> 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
> "Vivek" <Vivek@.discussions.microsoft.com> wrote in message
> news:6196820D-E5C6-4D83-97F1-0D7087B6AEC6@.microsoft.com...
>
>

optimizing load performance using partitioned tables in 2005

Hi,
Iam curious to know if i can increase my load performance using
partitioned tables. If i create a 4-way partitioned table, can i load
directly into a specific partition, so effectively having 4 parallel loads
into the 4 partitions. (Something i can do in Sybase).
If not what can i do to maximize my load on a partitioned table?
VivekThis is one option, but does your data align itself with these partitions?
Supposed you partition on last name, are your last names going to be evenly
distributed? Last names are a pretty good choice as the distribution is
somewhat even, however a choice like date is probably not so good if your
data is ordered already.
--
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
"Vivek" <Vivek@.discussions.microsoft.com> wrote in message
news:6196820D-E5C6-4D83-97F1-0D7087B6AEC6@.microsoft.com...
> Hi,
> Iam curious to know if i can increase my load performance using
> partitioned tables. If i create a 4-way partitioned table, can i load
> directly into a specific partition, so effectively having 4 parallel loads
> into the 4 partitions. (Something i can do in Sybase).
> If not what can i do to maximize my load on a partitioned table?
> Vivek|||Yes my data does align itself with these partitions. So what i want to know
is how do I load data into a specific partition? (using say BCP or Bulk
Insert) What is the syntax?
"Hilary Cotter" wrote:
> This is one option, but does your data align itself with these partitions?
> Supposed you partition on last name, are your last names going to be evenly
> distributed? Last names are a pretty good choice as the distribution is
> somewhat even, however a choice like date is probably not so good if your
> data is ordered already.
> --
> 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
> "Vivek" <Vivek@.discussions.microsoft.com> wrote in message
> news:6196820D-E5C6-4D83-97F1-0D7087B6AEC6@.microsoft.com...
> >
> > Hi,
> >
> > Iam curious to know if i can increase my load performance using
> > partitioned tables. If i create a 4-way partitioned table, can i load
> > directly into a specific partition, so effectively having 4 parallel loads
> > into the 4 partitions. (Something i can do in Sybase).
> > If not what can i do to maximize my load on a partitioned table?
> >
> > Vivek
>
>

Wednesday, March 28, 2012

optimizing a query to delete duplicates

I have a DELETE statement that deletes duplicate data from a table. It
takes a long time to execute, so I thought I'd seek advice here. The
structure of the table is little funny. The following is NOT the table,
but the representation of the data in the table:

+----+
| a | b |
+--+--+
| 123 | 234 |
| 345 | 456 |
| 123 | 123 |
+--+--+

As you can see, the data is tabular. This is how it is stored in the table:

+--+----+----+
| Row | FieldName | FieldValue |
+--+----+----+
| 1 | a | 123 |
| 1 | b | 234 |
| 2 | a | 345 |
| 2 | b | 456 |
| 3 | a | 123 |
| 3 | b | 234 |
+--+----+----+

What I need is to delete all records having the same "Row" when there exists
the same set of records with a different (smaller, to be precise) "Row".
Using the example above, what I need to get is:

+--+----+----+
| Row | FieldName | FieldValue |
+--+----+----+
| 1 | a | 123 |
| 1 | b | 234 |
| 2 | a | 345 |
| 2 | b | 456 |
+--+----+----+

A slow way of doing this seem to be:

DELETE FROM X
WHERE Row IN
(SELECT DISTINCT Row FROM X x1
WHERE EXISTS
(SELECT * FROM X x2
WHERE x2.Row < x1.Row
AND NOT EXISTS
(SELECT * FROM X x3
WHERE x3.Row = x2.Row
AND x3.FieldName = x2.FieldName
AND x3.FieldValue <> x1.FieldValue)))

Can this be done faster, better, and cheaper?my knee-jerk reaction is:

Why is it important to optimize it? I think you should delete the
duplicates, then create a constraint that prevents them from recurring.

If, for some reason, you are unable to fix the application that creates
these duplicates, and creating a constraint causes errors in the application
that you can't tolerate, then I suppose an alternative would be to create a
trigger that deletes them upon entry. Having a composite index on the
columns that are being duplicated would enable such a trigger to run
quickly.

But looking at your query, I find it strangely complex.

Why not just:

DELETE FROM X
WHERE EXISTS (SELECT * FROM X x2
WHERE x2.Row < x.Row
AND X.FieldName = x2.FieldName
AND X.FieldValue = x2.FieldValue)

Am I missing something? Your NOT EXISTS has me a bit confused... I think it
might delete data in situations other than described.

Also, NOT EXISTS is generally slow.|||On 2004-07-15, Aaron W. West <tallpeak@.hotmail.NO.SPAM> wrote:
> Why is it important to optimize it? I think you should delete the
> duplicates, then create a constraint that prevents them from recurring.

Such constraint may not be created. This table is a temporary table, where
data from an input file is loaded. Duplicate sets of records must be
deleted because the data then goes into permanent tables. Those table have
constraints against duplicates.

> But looking at your query, I find it strangely complex.

Me too. I'm trying to improve it. Its complexity seems to hinder its
performance.

> Why not just:
> DELETE FROM X
> WHERE EXISTS (SELECT * FROM X x2
> WHERE x2.Row < x.Row
> AND X.FieldName = x2.FieldName
> AND X.FieldValue = x2.FieldValue)

This would delete records that should not be deleted. Here's an example:

+--+----+----+
| Row | FieldName | FieldValue |
+--+----+----+
| 1 | a | 123 |
| 1 | b | 234 |
| 2 | a | 345 |
| 2 | b | 456 |
| 3 | a | 123 |
| 3 | b | 666 |
+--+----+----+

Here the combination of values for "a" and "b" on every "Row" is
different. There are no duplicates here. The query that you proposed would
delete the second to last row

+--+----+----+
| 3 | a | 123 |
+--+----+----+

because it has the same FieldName and FieldValue as the first row.

Think of it the data this way:

+--+--+
| a | b |
+--+--+
| 123 | 234 |
| 345 | 456 |
| 123 | 666 |
+--+--+

No duplicate rows here.|||Hi

You could try only selecting the correct data when you move it into the
permanent tables. But the following may work better:

DELETE FROM X1
FROM X X1 JOIN X X2
ON x2.Row < x1.Row
AND x1.Fieldvalue = x2.Fieldvalue
AND x1.FieldName = x2.FieldName

John

"Alexander Anderson" <no@.spam.com> wrote in message
news:slrncfe0ft.mk1.alex@.Toronto-HSE-ppp3682122.sympatico.ca...
> I have a DELETE statement that deletes duplicate data from a table. It
> takes a long time to execute, so I thought I'd seek advice here. The
> structure of the table is little funny. The following is NOT the table,
> but the representation of the data in the table:
> +----+
> | a | b |
> +--+--+
> | 123 | 234 |
> | 345 | 456 |
> | 123 | 123 |
> +--+--+
> As you can see, the data is tabular. This is how it is stored in the
table:
> +--+----+----+
> | Row | FieldName | FieldValue |
> +--+----+----+
> | 1 | a | 123 |
> | 1 | b | 234 |
> | 2 | a | 345 |
> | 2 | b | 456 |
> | 3 | a | 123 |
> | 3 | b | 234 |
> +--+----+----+
> What I need is to delete all records having the same "Row" when there
exists
> the same set of records with a different (smaller, to be precise) "Row".
> Using the example above, what I need to get is:
> +--+----+----+
> | Row | FieldName | FieldValue |
> +--+----+----+
> | 1 | a | 123 |
> | 1 | b | 234 |
> | 2 | a | 345 |
> | 2 | b | 456 |
> +--+----+----+
> A slow way of doing this seem to be:
> DELETE FROM X
> WHERE Row IN
> (SELECT DISTINCT Row FROM X x1
> WHERE EXISTS
> (SELECT * FROM X x2
> WHERE x2.Row < x1.Row
> AND NOT EXISTS
> (SELECT * FROM X x3
> WHERE x3.Row = x2.Row
> AND x3.FieldName = x2.FieldName
> AND x3.FieldValue <> x1.FieldValue)))
> Can this be done faster, better, and cheaper?

Optimizing a large

Hi
I'm running a script that updates the same column on every row in a huge table. The table is being updated with data from another table in another database, but they are in the same SQL instance
The log and data files for the database being read from are on an internal drive (D:\). The log and datafiles for the database being updated are on an external SAN (X:\
Would any of the following speed the process up significantly
1)Remove the indexes from the table being updated
2)Set the recovery model to 'simple' on the database being updated
3)Put the log and data files on the database being updated on different physical disks (not necessary i believe if I perform number 2)
thanks
KevinKevin,
1. If the column that you are updating has an index on it you might
consider dropping it until after the update.
2. This won't affect in any way the number of items logged or the amount of
data logged during an Update. If you do the update in one transaction it
won't matter either way.
3. It's always best to place the Log and Data files on separate drive
arrays. It makes no difference where the log for the one being read from
are since the log is not used for reads.
I would recommend you attempt the Updates in smaller batches. This will
keep the log in check and usually results in a faster overall operation.
You can usually achieve this with a loop of some sort.
--
Andrew J. Kelly
SQL Server MVP
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:715A5D36-5F4F-43D5-8B5B-854B711F3A9A@.microsoft.com...
> Hi,
> I'm running a script that updates the same column on every row in a huge
table. The table is being updated with data from another table in another
database, but they are in the same SQL instance.
> The log and data files for the database being read from are on an internal
drive (D:\). The log and datafiles for the database being updated are on an
external SAN (X:\)
> Would any of the following speed the process up significantly?
> 1)Remove the indexes from the table being updated?
> 2)Set the recovery model to 'simple' on the database being updated?
> 3)Put the log and data files on the database being updated on different
physical disks (not necessary i believe if I perform number 2).
> thanks,
> Kevin|||Thanks for your response
Does the frequency of log checkpoints significantly slow down a long running update statement? Should I create a large transaction log that will not need to dynamically grow and set the recovery interval to a high value so that the frequency of checkpoints is reduced
thanks
Kevin|||Kevin,
First off you should always have the log file larger than it needs to be for
any given operation. Anytime it has to grow it will impact performance to
some degree. As for check points that depends. Checkpoints certainly can
add overhead to the system, especially disk IO and CPU. Whether they have a
large negative effect on your situation is hard to say from here. If you
make a large recovery interval it will most definitely adversely affect the
other users when it does happen as there will be a lot more to do at one
time. The key in your situation is to do the updates in smaller batches.
Andrew J. Kelly
SQL Server MVP
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:4A65CF81-2DD6-4F53-AA7A-E7B00FF50CE2@.microsoft.com...
> Thanks for your response.
> Does the frequency of log checkpoints significantly slow down a long
running update statement? Should I create a large transaction log that will
not need to dynamically grow and set the recovery interval to a high value
so that the frequency of checkpoints is reduced?
> thanks,
> Kevin

Optimizing a large

Hi,
I'm running a script that updates the same column on every row in a huge tab
le. The table is being updated with data from another table in another datab
ase, but they are in the same SQL instance.
The log and data files for the database being read from are on an internal d
rive (D:\). The log and datafiles for the database being updated are on an e
xternal SAN (X:\)
Would any of the following speed the process up significantly?
1)Remove the indexes from the table being updated?
2)Set the recovery model to 'simple' on the database being updated?
3)Put the log and data files on the database being updated on different phys
ical disks (not necessary i believe if I perform number 2).
thanks,
KevinKevin,
1. If the column that you are updating has an index on it you might
consider dropping it until after the update.
2. This won't affect in any way the number of items logged or the amount of
data logged during an Update. If you do the update in one transaction it
won't matter either way.
3. It's always best to place the Log and Data files on separate drive
arrays. It makes no difference where the log for the one being read from
are since the log is not used for reads.
I would recommend you attempt the Updates in smaller batches. This will
keep the log in check and usually results in a faster overall operation.
You can usually achieve this with a loop of some sort.
Andrew J. Kelly
SQL Server MVP
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:715A5D36-5F4F-43D5-8B5B-854B711F3A9A@.microsoft.com...

quote:

> Hi,
> I'm running a script that updates the same column on every row in a huge

table. The table is being updated with data from another table in another
database, but they are in the same SQL instance.

quote:

> The log and data files for the database being read from are on an internal

drive (D:\). The log and datafiles for the database being updated are on an
external SAN (X:\)

quote:

> Would any of the following speed the process up significantly?
> 1)Remove the indexes from the table being updated?
> 2)Set the recovery model to 'simple' on the database being updated?
> 3)Put the log and data files on the database being updated on different

physical disks (not necessary i believe if I perform number 2).

quote:

> thanks,
> Kevin

|||Thanks for your response.
Does the frequency of log checkpoints significantly slow down a long running
update statement? Should I create a large transaction log that will not nee
d to dynamically grow and set the recovery interval to a high value so that
the frequency of checkpoint
s is reduced?
thanks,
Kevin|||Kevin,
First off you should always have the log file larger than it needs to be for
any given operation. Anytime it has to grow it will impact performance to
some degree. As for check points that depends. Checkpoints certainly can
add overhead to the system, especially disk IO and CPU. Whether they have a
large negative effect on your situation is hard to say from here. If you
make a large recovery interval it will most definitely adversely affect the
other users when it does happen as there will be a lot more to do at one
time. The key in your situation is to do the updates in smaller batches.
Andrew J. Kelly
SQL Server MVP
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:4A65CF81-2DD6-4F53-AA7A-E7B00FF50CE2@.microsoft.com...

quote:

> Thanks for your response.
> Does the frequency of log checkpoints significantly slow down a long

running update statement? Should I create a large transaction log that will
not need to dynamically grow and set the recovery interval to a high value
so that the frequency of checkpoints is reduced?

quote:

> thanks,
> Kevin

Optimizing a JOIN

I have two tables.

One has approx 90,000 rows with a field .. let's call in BigInt (and it
is defined as a bigint data type).

I have a reference table, with approx 10,000,000 rows. In this
reference table, I have starting_bigint and ending_bigint fields. I
want to pull out all of the reference data from the reference table for
all 90,000 rows in the transaction table where the BigInt from the
transaction table is between the starting_bigint and ending_bigint in
the reference table.

I have the join working now, but it is not as optimized as I would
like. It appears no matter what I do, the query does a full table scan
on the 10,000,000 rows in the reference table.

Sample code

SELECT ref.*, tran.bigint
from transactiontable tran
INNER JOIN referencetable ref on tran.bigint between
ref.starting_bigint and ending_bigint

Yes, all 3 of the fields are indexed. I even have a composite index on
the reference table with the starting_bigint and ending_bigint fields
selected as the composite.

Any help would be appreciated.

Robert H. Kershberg
IT Director
Tax Credit Company
RKershberg@.taxcc.com or RKershberg@.pobox.com or RKershberg@.gmail.comIf the starting and ending bigints ranges are not overlapping, then I
would classify this as the "zipcode problem".

If this is the case, you could try the following approach:

SELECT R1.*,T1.bigint
FROM transactiontable T1
CROSS JOIN referencetable R1
WHERE R1.starting_bigint = (
SELECT MAX(starting_bigint)
FROM referencetable R2
WHERE R2.starting_bigint <= T1.bigint
)
AND R1.ending_bigint >= T1.bigint

Hope this helps,
Gert-Jan

"rkershberg@.gmail.com" wrote:
> I have two tables.
> One has approx 90,000 rows with a field .. let's call in BigInt (and it
> is defined as a bigint data type).
> I have a reference table, with approx 10,000,000 rows. In this
> reference table, I have starting_bigint and ending_bigint fields. I
> want to pull out all of the reference data from the reference table for
> all 90,000 rows in the transaction table where the BigInt from the
> transaction table is between the starting_bigint and ending_bigint in
> the reference table.
> I have the join working now, but it is not as optimized as I would
> like. It appears no matter what I do, the query does a full table scan
> on the 10,000,000 rows in the reference table.
> Sample code
> SELECT ref.*, tran.bigint
> from transactiontable tran
> INNER JOIN referencetable ref on tran.bigint between
> ref.starting_bigint and ending_bigint
> Yes, all 3 of the fields are indexed. I even have a composite index on
> the reference table with the starting_bigint and ending_bigint fields
> selected as the composite.
> Any help would be appreciated.
> Robert H. Kershberg
> IT Director
> Tax Credit Company
> RKershberg@.taxcc.com or RKershberg@.pobox.com or RKershberg@.gmail.com|||try skipping the "between." something like
SELECT ref.*, tran.bigint
from transactiontable tran
INNER JOIN referencetable ref
on tran.bigint >= ref.starting_bigint
and tran.bigint <=ref.ending_bigint

try creating a composite index on ref, containing starting_bigint plus
ending_bigint
if that doesn't work, try zapping your existing indexes on ref.

thinking outside the box, try creating staging tables, or ghost tables.
The ghost tables are for a selected period taht you are currently
working on. You copy pieces of your trans table off to a temp table,
and do your lookups against it. you eat the one copy, you eat the
creation of the indexon the temp table, but you get the benefits of not
having to do complex searches against a 100,000,000 row table. this is
RARELY needed, but I've done it on rare occasion.|||Thank you for your time. I had tried a cross join, but not quite the
same way. It'll be interesting to see if there is a significant
difference. I appreciate your intelligent input.

Rob|||Thanks again .. this logic improved performance 3 to 4 fold (I had
already cut a 2 min 30 sec query to 15 seconds .. your logic
implemented on top of mine cut that down to 3 seconds .. this on 88,000
row transacation table running against a 9.6 million row reference
table .. not zip codes, but the right idea <g>).

Thank YOU very much!!!

Rob|||3 seconds is a long time.

how do we make it faster??

i am not a huge fan of clustered indexes. However for a reference
table where you will ALWAYS be looking up data utilizing a specific
column, and rarely inserting into the middle, I'd sure consider it.

For grins, try creating a clustered, composite index on starting_bigint
+ ending_bigint.

For grins after that, consider a clustered index on trans.bigint.
warning: clustered indexes on trans tables are usually not a great idea
IMO, unless the clustered index is based on a timestamp or an identity
column.

-doug

Monday, March 26, 2012

optimizer problem

Hi,
We have a table having 3.2 million rows having primary key
clustered index on id column ...update statistics is done
with fullscan(100%)...
when we are running:
select count(*) from table1 ...it is taking about 4
minutes to return the result...when i see the statistics
io it shows that it is doing scan count:728...
How can this be doing scan count 728 on 2 cpu machine and
takes 4 min just to return count?
Thanks
--HarvinderIf it actually is a scan count of 728, that is not the same as Logical
reads. It means that SQL Server is accessing the table 728 times, and this
usually implies some sort of join.
Can you SET STATISTICS PROFILE ON and show us the output so we can see the
query plan in addition to the statistics?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"harvinder" <hs@.metratech.com> wrote in message
news:026401c3522b$6b4cea20$a601280a@.phx.gbl...
> Hi,
> We have a table having 3.2 million rows having primary key
> clustered index on id column ...update statistics is done
> with fullscan(100%)...
> when we are running:
> select count(*) from table1 ...it is taking about 4
> minutes to return the result...when i see the statistics
> io it shows that it is doing scan count:728...
> How can this be doing scan count 728 on 2 cpu machine and
> takes 4 min just to return count?
> Thanks
> --Harvinder
>|||That was my other question...howcome it is doing 728 scan
count instead of 1 clustered index scan...i am pasting the
output of showplan :
select count(*) from tab1
|--Compute Scalar(DEFINE:([Expr1002]=Convert
([globalagg1004])))
|--Stream Aggregate(DEFINE:([globalagg1004]=SUM
([partialagg1003])))
|--Parallelism(Gather Streams)
|--Stream Aggregate(DEFINE:
([partialagg1003]=Count(*)))
|--Clustered Index Scan(OBJECT:([dm].
[dbo].[tab1].[pk_tab1]))
Thanks
--Harvinder
>--Original Message--
>If it actually is a scan count of 728, that is not the
same as Logical
>reads. It means that SQL Server is accessing the table
728 times, and this
>usually implies some sort of join.
>Can you SET STATISTICS PROFILE ON and show us the output
so we can see the
>query plan in addition to the statistics?
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"harvinder" <hs@.metratech.com> wrote in message
>news:026401c3522b$6b4cea20$a601280a@.phx.gbl...
>> Hi,
>> We have a table having 3.2 million rows having primary
key
>> clustered index on id column ...update statistics is
done
>> with fullscan(100%)...
>> when we are running:
>> select count(*) from table1 ...it is taking about 4
>> minutes to return the result...when i see the
statistics
>> io it shows that it is doing scan count:728...
>> How can this be doing scan count 728 on 2 cpu machine
and
>> takes 4 min just to return count?
>> Thanks
>> --Harvinder
>
>.
>|||I was actually hoping for the STATISTICS PROFILE output in addition to the
exact STATISTICS IO that I assumed you were already collecting.
My guess at this point (without seeing the STATISTICS IO output) is that
the high scan count is related to the fact that the query is being processed
in parallel.
The large amount of time is probably because of the clustered index scan. A
clustered index scan is exactly the same as a table scan, so to get the
results of count(*) SQL Server has to look at every row on every page. How
many rows and how many pages are in this table? Does the query include a
WHERE clause? What is the result of your count(*) query?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"harvinder" <hs@.metratech.com> wrote in message
news:051501c35230$b8aed800$a301280a@.phx.gbl...
> That was my other question...howcome it is doing 728 scan
> count instead of 1 clustered index scan...i am pasting the
> output of showplan :
> select count(*) from tab1
> |--Compute Scalar(DEFINE:([Expr1002]=Convert
> ([globalagg1004])))
> |--Stream Aggregate(DEFINE:([globalagg1004]=SUM
> ([partialagg1003])))
> |--Parallelism(Gather Streams)
> |--Stream Aggregate(DEFINE:
> ([partialagg1003]=Count(*)))
> |--Clustered Index Scan(OBJECT:([dm].
> [dbo].[tab1].[pk_tab1]))
> Thanks
> --Harvinder
> >--Original Message--
> >If it actually is a scan count of 728, that is not the
> same as Logical
> >reads. It means that SQL Server is accessing the table
> 728 times, and this
> >usually implies some sort of join.
> >Can you SET STATISTICS PROFILE ON and show us the output
> so we can see the
> >query plan in addition to the statistics?
> >
> >--
> >HTH
> >--
> >Kalen Delaney
> >SQL Server MVP
> >www.SolidQualityLearning.com
> >
> >
> >"harvinder" <hs@.metratech.com> wrote in message
> >news:026401c3522b$6b4cea20$a601280a@.phx.gbl...
> >> Hi,
> >>
> >> We have a table having 3.2 million rows having primary
> key
> >> clustered index on id column ...update statistics is
> done
> >> with fullscan(100%)...
> >> when we are running:
> >> select count(*) from table1 ...it is taking about 4
> >> minutes to return the result...when i see the
> statistics
> >> io it shows that it is doing scan count:728...
> >> How can this be doing scan count 728 on 2 cpu machine
> and
> >> takes 4 min just to return count?
> >>
> >> Thanks
> >> --Harvinder
> >>
> >
> >
> >.
> >|||if your system is a Xeon or Xeon MP, and HT is enabled,
and you have a parallel execution plan
try OPTION (MAXDOP 1)
better yet, disabled HT
>--Original Message--
>Hi,
>We have a table having 3.2 million rows having primary
key
>clustered index on id column ...update statistics is done
>with fullscan(100%)...
>when we are running:
>select count(*) from table1 ...it is taking about 4
>minutes to return the result...when i see the statistics
>io it shows that it is doing scan count:728...
>How can this be doing scan count 728 on 2 cpu machine and
>takes 4 min just to return count?
>Thanks
>--Harvinder
>.
>

Friday, March 30, 2012

Optimizing table with more than 54 million records

Optimizing table with more than 54 million records

Optimizing table with more than 54 million records

Optimizing table in mssql

Optimizing SQL Query performance

Optimizing SQL - Union

Optimizing SELECT query

Optimizing query with UDF and table vars and IN

Optimizing queries

Optimizing Lookups on a trigger's INSERTED virtual table

Optimizing Lookups on a trigger's INSERTED virtual table

Optimizing Lookups on a trigger's INSERTED virtual table

optimizing load performance using partitioned tables in 2005

optimizing load performance using partitioned tables in 2005

optimizing load performance using partitioned tables in 2005

Wednesday, March 28, 2012

optimizing a query to delete duplicates

Optimizing a large

Optimizing a large

Optimizing a JOIN

Monday, March 26, 2012

optimizer problem

OpenXML Identity Insert

Blog Archive

About Me