optimizing resource allocation

Hi All,
Some of the queries we are running are resource intensive which is impacting
other users running other processes on the same SQL server. Is there a way
to limit the time allocated to a query so it pauses after x seconds in order
to allow other processes to run? Please note that the query should not be
permanently interrupted but temporarily halted.
I have checked the "query governor" option but it seems that this parameter
would disallow running the query if it exceeds the cost limit.
Regards
Hi
Perhaps it's time to start to optimize your queries.Compare an execution
plan , make sure that the optimizer is available to use indexes defined on
the tables.
"imad koussa" <imad.koussa@.aims-co.com> wrote in message
news:uu6kex0FGHA.2444@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> Some of the queries we are running are resource intensive which is
> impacting other users running other processes on the same SQL server. Is
> there a way to limit the time allocated to a query so it pauses after x
> seconds in order to allow other processes to run? Please note that the
> query should not be permanently interrupted but temporarily halted.
> I have checked the "query governor" option but it seems that this
> parameter would disallow running the query if it exceeds the cost limit.
> Regards
>
|||Basically no there is no easy or practical way to do that. But if the
resources are CPU you should consider using a MAXDOP hint in those queries
to limit the number of CPU's they can use in parallel.
Andrew J. Kelly SQL MVP
"imad koussa" <imad.koussa@.aims-co.com> wrote in message
news:uu6kex0FGHA.2444@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> Some of the queries we are running are resource intensive which is
> impacting other users running other processes on the same SQL server. Is
> there a way to limit the time allocated to a query so it pauses after x
> seconds in order to allow other processes to run? Please note that the
> query should not be permanently interrupted but temporarily halted.
> I have checked the "query governor" option but it seems that this
> parameter would disallow running the query if it exceeds the cost limit.
> Regards
>

optimizing resource allocation

Hi All,
Some of the queries we are running are resource intensive which is impacting
other users running other processes on the same SQL server. Is there a way
to limit the time allocated to a query so it pauses after x seconds in order
to allow other processes to run? Please note that the query should not be
permanently interrupted but temporarily halted.
I have checked the "query governor" option but it seems that this parameter
would disallow running the query if it exceeds the cost limit.
RegardsHi
Perhaps it's time to start to optimize your queries.Compare an execution
plan , make sure that the optimizer is available to use indexes defined on
the tables.
"imad koussa" <imad.koussa@.aims-co.com> wrote in message
news:uu6kex0FGHA.2444@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> Some of the queries we are running are resource intensive which is
> impacting other users running other processes on the same SQL server. Is
> there a way to limit the time allocated to a query so it pauses after x
> seconds in order to allow other processes to run? Please note that the
> query should not be permanently interrupted but temporarily halted.
> I have checked the "query governor" option but it seems that this
> parameter would disallow running the query if it exceeds the cost limit.
> Regards
>|||Basically no there is no easy or practical way to do that. But if the
resources are CPU you should consider using a MAXDOP hint in those queries
to limit the number of CPU's they can use in parallel.
Andrew J. Kelly SQL MVP
"imad koussa" <imad.koussa@.aims-co.com> wrote in message
news:uu6kex0FGHA.2444@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> Some of the queries we are running are resource intensive which is
> impacting other users running other processes on the same SQL server. Is
> there a way to limit the time allocated to a query so it pauses after x
> seconds in order to allow other processes to run? Please note that the
> query should not be permanently interrupted but temporarily halted.
> I have checked the "query governor" option but it seems that this
> parameter would disallow running the query if it exceeds the cost limit.
> Regards
>

optimizing resource allocation

Hi All,
Some of the queries we are running are resource intensive which is impacting
other users running other processes on the same SQL server. Is there a way
to limit the time allocated to a query so it pauses after x seconds in order
to allow other processes to run? Please note that the query should not be
permanently interrupted but temporarily halted.
I have checked the "query governor" option but it seems that this parameter
would disallow running the query if it exceeds the cost limit.
RegardsHi
Perhaps it's time to start to optimize your queries.Compare an execution
plan , make sure that the optimizer is available to use indexes defined on
the tables.
"imad koussa" <imad.koussa@.aims-co.com> wrote in message
news:uu6kex0FGHA.2444@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> Some of the queries we are running are resource intensive which is
> impacting other users running other processes on the same SQL server. Is
> there a way to limit the time allocated to a query so it pauses after x
> seconds in order to allow other processes to run? Please note that the
> query should not be permanently interrupted but temporarily halted.
> I have checked the "query governor" option but it seems that this
> parameter would disallow running the query if it exceeds the cost limit.
> Regards
>|||Basically no there is no easy or practical way to do that. But if the
resources are CPU you should consider using a MAXDOP hint in those queries
to limit the number of CPU's they can use in parallel.
--
Andrew J. Kelly SQL MVP
"imad koussa" <imad.koussa@.aims-co.com> wrote in message
news:uu6kex0FGHA.2444@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> Some of the queries we are running are resource intensive which is
> impacting other users running other processes on the same SQL server. Is
> there a way to limit the time allocated to a query so it pauses after x
> seconds in order to allow other processes to run? Please note that the
> query should not be permanently interrupted but temporarily halted.
> I have checked the "query governor" option but it seems that this
> parameter would disallow running the query if it exceeds the cost limit.
> Regards
>

Optimizing Queries generated at runtime

Hi

We are using SQL SERVER 2005, enterprise/standard edition for an application that generates queries at runtime and normally having many joins in it. But these queries are taking lot of time when they are executed. After setting the database parameter PARAMETERIZATION to FORCED, performance of the queries have improved a lot but still we want to improve them further. Is there any other parameter that we can set for improving the performance of the queries. We have created the indexes on the basic columns that will be used while querying but it may happen that some other columns may also be used for searching in which case queries become quiet slow. Is there anything like skip-scan indexes(of oracle) in sql server 2005.

Regards
Salil

Check if there are columns that participate in WHERE clause and have not indexes.

You say "an application that generates queries at runtime", try to use parametrized stored procedures that build dynamically queries using sp_executesql;

in queries don't use "select * from ..." use "select col1,col2,... from ..."

try to use WHERE clause in queries to force using indexes.

you say your queries have many joins on it, try to use indexed views, see article|||1. We have created the indexes on the main columns that we know will be included in the where clause but there can be some columns, we dont know, that can be included in the where clause.
2. Going for parameterized stored procedures is a nice option but we cant go for them at this stage of our product.
3. We are using column names in the select clause rather than "select * "
4. We have many joins but we dont know join will be made to which table.

Thats why we are looking for something more generic like the PARAMETERIZATION parameter. Is there anything else that we can set at the database level for improving the performance of our queries.

|||

Salil wrote:

1. We have created the indexes on the main columns that we know will be included in the where clause but there can be some columns, we dont know, that can be included in the where clause.
2. Going for parameterized stored procedures is a nice option but we cant go for them at this stage of our product.
3. We are using column names in the select clause rather than "select * "
4. We have many joins but we dont know join will be made to which table.

I think you work with a black box and you can optimize outside it; i think you haven't many choices

Check if database options related to STATISTICS are on "ON" option.

So, I think you have to open that black box....

|||we have already set AUTO_UPDATE_STATISTICS_ASYNC ON at the database level.

optimizing queries

Hi,
I am about to start system optimization and I have a general question about
how to proceed.
I am a coldfusion programmer and working on a lagre system with a lot of db
interaction. Recently, our isp upgraded to SQL 2000 and I started using
functions quite extensively for validation and calculations. I have a lot of
large queries that I am planning to transfer from coldfusion scripts to sql.
My question is:
SQL Server has few options for queries: stored procedures (I use them a lot
for complex logic), views and functions. What should I use for queries? For
example views can be indexed same as tables, stored procedures are compiled
and last one is functions (I dont think is such a good idea to store queries
but they can return tables). What are general recommendations?
Thanks in advance,
GeneThe answer: It depends.
If you can take part of your complex operation and make an indexed view, it
is certainly worthwhile, esp. if this view of information is used in more
than one procedure.
If the index will not help and there is no reuse, the procedures are the
best avenue for most work that performs CRUD on one or more tables.
Functions are used to solve problems outside of the data set realm (at
least, in general), like special calculations of aggregations or date
formatting outside of what is natively in SQL Server. For example, we had a
function like:
SELECT OurCustomerDateFormat(DateField) FROM SomeTable
That was a function.
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"News" wrote:

> Hi,
> I am about to start system optimization and I have a general question abou
t
> how to proceed.
> I am a coldfusion programmer and working on a lagre system with a lot of d
b
> interaction. Recently, our isp upgraded to SQL 2000 and I started using
> functions quite extensively for validation and calculations. I have a lot
of
> large queries that I am planning to transfer from coldfusion scripts to sq
l.
> My question is:
> SQL Server has few options for queries: stored procedures (I use them a lo
t
> for complex logic), views and functions. What should I use for queries? Fo
r
> example views can be indexed same as tables, stored procedures are compile
d
> and last one is functions (I dont think is such a good idea to store queri
es
> but they can return tables). What are general recommendations?
> Thanks in advance,
> Gene
>
>|||>> I started using functions quite extensively for validation and calculatio
ns. <<
Usually a bad idea for validation. SQL is declarative and you want to
use CHECK() and DEFAULT clauses that apply to an entire column, rather
than triggers, functions and procedures which work on scalars.
I would go with stored procedures and VIEWs for the calculations.
Functions that return tables and indexed views are proprietary.

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 MDX?

Has anyone any knowledge of optimizing MDX?
I have several MDX queries I have inherited, which take (some) in excess of
a minute to return.
I have been through Spofford's book and others. None seem to address a best
practices approach.
Any suggestions.
Thank you
What do you have?
there are some practices what to avoid...
"rick" <Cadian31@.Earthlink.net> wrote in message
news:uAPJpryIEHA.3848@.tk2msftngp13.phx.gbl...
> Has anyone any knowledge of optimizing MDX?
> I have several MDX queries I have inherited, which take (some) in excess
of
> a minute to return.
> I have been through Spofford's book and others. None seem to address a
best
> practices approach.
> Any suggestions.
> Thank you
>
|||Here is an example.
The date dimension I know is baddly designed without any true unique members
at any level. This needs to change.
When I remove the filter for the sum > 0 the query is quite quick.
Otherwise it is sluggish....
any suggestions regarding the mdx structure. I am looking for a short term
improvement until I can re-design the cube.
Thanks in advance...
WITH
SET [DateRangeSpan] AS
'{ [DueDate].[YQMD].[All Dates].[2004].[Quarter 1].[February].[26]
:[DueDate].[YQMD].[All Dates].[2004].[Quarter 1].[March].[25] }'
SET [FuncUnitList] AS
'{[FuncUnit].&[Merchandise/Media]} '
SELECT
{[OrgUnit].&[1-990], [OrgUnit].&[1-990].CHILDREN} DIMENSION
PROPERTIES [OrgUnit].[Org Type] ON COLUMNS,
Subset (
FILTER (
CROSSJOIN (
{EXTRACT ( NONEMPTYCROSSJOIN ( [Activity].[Activity].Members,
[DateRangeSpan] , [FuncUnitList] ), Activity) },
{[Measures].[Stores Included],[Measures].[Stores
Complete],[Measures].[% Complete]})
, Sum({Axis(0)}, [Measures].[Raw Stores
Included]) > 0
)
, 0, 150
)
DIMENSION PROPERTIES [Activity].[Activity].[Event ID],
[Activity].[Activity].[Process ID], [Activity].[Activity].[Activity ID],
[Activity].[Activity].[Event Name], [Activity].[Activity].[Process
Name],
[Activity].[Activity].[Activity Due Date],
[Activity].[Activity].[FuncUnit Name] ON ROWS
FROM [CompletionMonitor]
"Andrej Hudoklin" <andrej.hudoklin@.add.si> wrote in message
news:OlgIkR3IEHA.3556@.TK2MSFTNGP10.phx.gbl...
> What do you have?
> there are some practices what to avoid...
>
> "rick" <Cadian31@.Earthlink.net> wrote in message
> news:uAPJpryIEHA.3848@.tk2msftngp13.phx.gbl...
> of
> best
>

optimizing MDX?

Has anyone any knowledge of optimizing MDX?
I have several MDX queries I have inherited, which take (some) in excess of
a minute to return.
I have been through Spofford's book and others. None seem to address a best
practices approach.
Any suggestions.
Thank youWhat do you have?
there are some practices what to avoid...
"rick" <Cadian31@.Earthlink.net> wrote in message
news:uAPJpryIEHA.3848@.tk2msftngp13.phx.gbl...
> Has anyone any knowledge of optimizing MDX?
> I have several MDX queries I have inherited, which take (some) in excess
of
> a minute to return.
> I have been through Spofford's book and others. None seem to address a
best
> practices approach.
> Any suggestions.
> Thank you
>|||Here is an example.
The date dimension I know is baddly designed without any true unique members
at any level. This needs to change.
When I remove the filter for the sum > 0 the query is quite quick.
Otherwise it is sluggish....
any suggestions regarding the mdx structure. I am looking for a short term
improvement until I can re-design the cube.
Thanks in advance...
WITH
SET [DateRangeSpan] AS
'{ [DueDate].[YQMD].[All Dates].[2004].[Quarter 1].
[February].[26]
:[DueDate].[YQMD].[All Dates].[2004].[Quarter 1].[Ma
rch].[25] }'
SET [FuncUnitList] AS
'{[FuncUnit].&[Merchandise/Media]} '
SELECT
{[OrgUnit].&[1-990], [OrgUnit].&[1-990].CHILDREN} DIMEN
SION
PROPERTIES [OrgUnit].[Org Type] ON COLUMNS,
Subset (
FILTER (
CROSSJOIN (
{EXTRACT ( NONEMPTYCROSSJOIN ( [Activity].[Activity].Members,
[DateRangeSpan] , [FuncUnitList] ), Activity) },
{[Measures].[Stores Included],[Measures].[Stores
Complete],[Measures].[% Complete]})
, Sum({Axis(0)}, [Measures].[Raw Stores
Included]) > 0
)
, 0, 150
)
DIMENSION PROPERTIES [Activity].[Activity].[Event ID],
[Activity].[Activity].[Process ID], [Activity].[Activity
].[Activity ID],
[Activity].[Activity].[Event Name], [Activity].[Activity
].[Process
Name],
[Activity].[Activity].[Activity Due Date],
[Activity].[Activity].[FuncUnit Name] ON ROWS
FROM [CompletionMonitor]
"Andrej Hudoklin" <andrej.hudoklin@.add.si> wrote in message
news:OlgIkR3IEHA.3556@.TK2MSFTNGP10.phx.gbl...
> What do you have?
> there are some practices what to avoid...
>
> "rick" <Cadian31@.Earthlink.net> wrote in message
> news:uAPJpryIEHA.3848@.tk2msftngp13.phx.gbl...
> of
> best
>sql

Optimized Qeries without using Sub-Correlated Queries

Hi,

I am used to writing Sub-Correlated queries within my main queries. Although they work fine but i have read alot that they have performance hits. Also, as with time our data has increased, a simple SELECT statement with a few Sub-Queries tends to run slower which may be between 10-15 seconds. Following will be a simple example of what i mostly do:

SELECT DISTINCT C.CusID, C.Name, C.Age,

(

SELECT SUM (Price)

FROM CusotmerOrder

WHERE CusID_fk = CO.CusID_fk

) Total_Order_Price,

(

SELECT SUM (Concession)

FROM CusotmerOrder

WHERE CusID_fk = CO.CusID_fk

) Total_Order_Concession,

(

SELECT SUM (Price) - SUM (Concession)

FROM CusotmerOrder

WHERE CusID_fk = CO.CusID_fk

) Total_Difference

FROM Customer C

INNER JOIN CustomerOrder CO

ON C.CusID = CO.CusID_fk

.....

WHERE (conditions...)

My question is what would be a better way to handle the above query? How can i write a better yet simple query with optimized performance. I would also mention that in some of my asp.net applications, i use inline queries assigned to SqlCommand Object. The reason i mention it that since these queries are written in some class files, how would we still accomplish what i have mentioned above.Kindly could anyQuery Guru guide me writing better queries. I shall be obliged...

SELECT CustomerID, SUM(Price), SUM(Concession), SUM(Price-Concession)

FROM CusttomerOrder

GROUP BY CustomerID

|||

You can also use the above as a derived table and join on it, like:

SELECT Customers.CustomerID, Customers.Name, Customers.Age, OrdersTotals.TotalPrice, OrdersTotals.TotalConcession, OrdersTotals.TotalDifference

FROM Customers

INNER JOIN (

SELECT CustomerID, SUM(Price) AS TotalPrice, SUM(Concession) AS TotalConcession, SUM(Price-Concession) AS TotalDifference

FROM Orders

) AS OrdersTotals

ON Customers.CustomerID = OrdersTotals.CustomerID

|||

Hi Valenumr,

Thanks alot man, wow, you made things look so easy. The queries i have been working with were really optimized and the time went down from 10 sec to 1 sec. Greate help man and I learnt a great new technique.

Valenumr, now I need one more improvement on the above query. What if I want to get the same query working but on a day by day basis? Actually I use this query to generate a weekly report but it takes a long time. Also I tried to use the same for a monthly report, but the time exceeded 5 minutes. How can I improve the following query (im using the date add function to add days to given date):

Declare @.DateStart DateTime

Set @.DateStart = '2006-05-01'

SELECT DISTINCT C.CusID, C.Name, C.Age,

(

SELECT SUM (Price)

FROM CusotmerOrder

WHERE CusID_fk = CO.CusID_fk

AND Shop_Date = dateadd(dd,0, @.DateStart)

) Customer_Sales_Day1,

(

SELECT SUM (Price)

FROM CusotmerOrder

WHERE CusID_fk = CO.CusID_fk

AND Shop_Date = dateadd(dd,1, @.DateStart)

) Customer_Sales_Day2,

.................. continued for seven days ..................

(

SELECT SUM (Price)

FROM CusotmerOrder

WHERE CusID_fk = CO.CusID_fk

AND Shop_Date = dateadd(dd,6, @.DateStart)

) Customer_Sales_Day7,

FROM Customer C ...

WHERE (condition)

I look forward to your great help, thanks a lot...

|||

I think you want something like this:

SELECT Customer.CustomerID, Customer.Name, Customer.Age, OrderSummary.Date, OrderSummary.TotalPriceFROM CustomersINNER JOIN ( SELECT CustomerID, SUM(Price) AS TotalPrice, Shop_Date AS Date FROM Orders GROUP BY CustomerID, Shop_Date HAVING Shop_Date BETWEEN Start_Date AND End_Date) AS OrderSummaryON Customers.CustomerID = OrdersSummary.CustomerID

|||

Hi Valenumr,

Thanks for this query once again. It really worked like a charm and taught me a new technique once again. If you dont mind helping a bit more, kindly can i ask for one more help? This query has worked absolutely fine without any problem for me. What if a record does not exists for any given date between the given dates. In that case, i would like to have a 0 for the sum. e.g. For a customer, if records exist for dates 15, 16, 17, 19, 20 and 21 May, 2006 and a record for 18th May does not exists, then i would want to inset a 0 for 18th May, 2006. i want the records to look like the following:

EmpID Name D1 D2 D3 D4 D5 D6 D7

1001 ABC 200 100 90 0 9 74 10

Once again thanks alot for all the help you have given. You help has been very valuable to me. Cheers...

|||

one more thing. i have observed that whenever i use the Group By clause, it asks me to include all the SELECT ColumnsNames in the Group By clause. the message is"Column 'ColumnName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause". is there a way that i can include only the required columns in the Group By clause.

|||

What you are trying to do is a pivot (or cross-tab) query. I am not very familiar with doing that in SQL, but there is a feature in SQL 2005. It would look something like this.

SELECT *FROM (SELECT EmpID,Name, Price,CONVERT(nvarchar(32), OrderDate, 106)AS OrderDateFROM OrdersWHERE OrderDateBETWEEN N'01/01/2007'AND N'01/07/2007')AS OrdersPIVOT (SUM(Price)FOR OrderDateIN ( [01 Jan 2007], [02 Jan 2007], [03 Jan 2007], [04 Jan 2007], [05 Jan 2007], [06 Jan 2007], [07 Jan 2007] ))AS PivotTable

Anyhow, for the date, you most likely want to do some type of conversion, otherwise the column names will have the long date format. I just used 106 as an example format, which is dd MMM yyyy, which you should note is how the pivot columns are named in the FOR ... IN (column_list).

|||

Hi Valenumr,

Yes your absolutely right that this is a PIVOT Technique. Some1 else also pointed out the same technique in a different post. Infact i do have the PIVOT Technique (not the built in PIVOT function) working. Anyways, can you answer one final question (hope its the last :)). Why do we have to include all the columns from the SELECT clause in our Group By clause otherwise it gives the message "Column 'ColumnName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause" or im mistaken in understanding this point.

Valenumn, thanx alot for your time and patience. If i could vote you to be a MVP, i would have :)

|||

Say you do something like "SELECT A, B, SUM(C)..."

It seems sort of silly from the point of view that A, B must be specified in the group by clause, since they are in the select list and you are using an aggregate function (SUM) in the select list. You would almost think that by default saying GROUP BY A, B is redundant.

Well, basically, the SUM(C) has to know what perform the aggregate sum over, and the GROUP BY A, B is like the distict set of A / B combinations in the record source (which you should see corresponds to your SELECT DISTINCT statements).

However, you could GROUP BY A, B, D, E, etc. event though D and E are not in the SELECT list. I guess it is probably easier from the SQL specification / implementation perspective to require the GROUP BY to have a complete specification, and that any columns in the select list that aren't aggregated exist in that specification.

It would be awkward to say "SELECT A, B, SUM(C)... GROUP BY D" and have that *imply" A and B in the group by list, because it isn't clear. Also, what if you say "SELECT SUM(C)...", so in this case, you must have *some* group by, or should the engine assume that if you don't, "GROUP BY C" is implied... well, then going back to the original example, should C be included in the GROUP BY list if none is specified? The results of GROUP BY A, B, C will be very different (usually) than GROUP BY A, B.

So, I guess there are just way too many cases to consider that would lack direct clarity if the spec was any different. The GROUP BY clause must be explicit.

Finally, Imagine this... "SELECT A, B, SUM(C)... GROUP BY D"... well, what does that mean exactly? how can you GROUP BY D when for every D you have more than one combination of A / B, so that doesn't really make sense (going back to the SELECT DISTINCT analogy).

optimize queries.

how to optimize the following queries..
1) SELECT * FROM employee
WHERE job_id not IN ( SELECT job_id
FROM jobs
WHERE min_lvl = 25 )
2) select * from tbla where name like '%F'
Regards
sanjaysanjay
It is to hard to say . You did not provide any info about how many data
exists in the table?
How often you query the table?
What is indexes on the table?
My guess.
1) SELECT * FROM employee
WHERE NOT EXISTS
( SELECT jobs .job_id
FROM jobs WHERE employee.job_id
=jobs.job_id AND min_lvl = 25 )

quote:

---

> 2) select * from tbla where name like '%F'

---

This one may prevent query optimyzer for using an index on the name column
unless you re-write it as 'F%'.
Consider to use covering indexes instead of select * from table .
For more details please refer to BOL
"sanjay" <anonymous@.discussions.microsoft.com> wrote in message
news:19EA7547-8EBC-43FF-9981-3AF635B34A03@.microsoft.com...

quote:

---

> how to optimize the following queries..
> 1) SELECT * FROM employee
> WHERE job_id not IN ( SELECT job_id
> FROM jobs
> WHERE min_lvl = 25 )
> 2) select * from tbla where name like '%F'
> Regards
> sanjay

---

|||hi
im using not In in my query..which as per documents/sqlserver etc..is not th
e best way to use and it hampers performance...how i can avoide not In..
execution plan of Not In/ Not exists are same..
suppost i ve 1 million records in this tables and its being heavily used..
both are normal tables from pUbs database..
==========
2) if we rewrite the query as 'F%' this will change the logic of the query..
keeping same logic how can we modify the query.|||sanjay
1) Look , another approach but you will have to compare all methods .
1) SELECT * FROM employee E LEFT JOIN jobs J
ON E.job_id=J.job_id WHERE min_lvl = 25 AND J.job_id IS NOT NULL
2)
http://www.sql-server-performance.c...ing_indexes.asp
"sanjay" <anonymous@.discussions.microsoft.com> wrote in message
news:AFD7096E-56CF-4C1C-B71D-F4862A8EA60D@.microsoft.com...

quote:

---

> hi
> im using not In in my query..which as per documents/sqlserver etc..is not

---

the best way to use and it hampers performance...how i can avoide not In..

quote:

---

> execution plan of Not In/ Not exists are same..
> suppost i ve 1 million records in this tables and its being heavily used..
> both are normal tables from pUbs database..
> ==========
> 2) if we rewrite the query as 'F%' this will change the logic of the

---

query..keeping same logic how can we modify the query.

quote:

---

>

---

|||hi
i tried ..but seems some problem.
SELECT * FROM employee
WHERE job_id not IN ( SELECT job_id
FROM jobs
WHERE min_lvl = 25 )
the above query returned 34 rows...whereas the below query only 9 rows...i t
hink its using left join bcoz of that..
SELECT * FROM employee E LEFT JOIN jobs J
ON E.job_id=J.job_id WHERE min_lvl = 25 AND J.job_id IS NOT NULL
any further updates would be highly appreciated..
regards|||sanjay
Can post DDL + sample data + expected result and then I can answer you why
it returns 9 rows and why it returns 34 rows.
"sanjay" <anonymous@.discussions.microsoft.com> wrote in message
news:60500162-DCCE-4D7A-9B3F-BE4A3C50B05C@.microsoft.com...

quote:

---

> hi
> i tried ..but seems some problem.
> SELECT * FROM employee
> WHERE job_id not IN ( SELECT job_id
> FROM jobs
> WHERE min_lvl = 25 )
> the above query returned 34 rows...whereas the below query only 9 rows...i

---

think its using left join bcoz of that..

quote:

---

> SELECT * FROM employee E LEFT JOIN jobs J
> ON E.job_id=J.job_id WHERE min_lvl = 25 AND J.job_id IS NOT NULL
> any further updates would be highly appreciated..
> regards
>

---

|||Try below query :
select e.* from employee e, ( SELECT job_id
FROM jobs
WHERE min_lvl <> 25 ) a
where e.job_id = a.job_id
This would increase performance, as for each of the job_id, it wont scan
jobs table.
- Ketan
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OFu0osW6DHA.2460@.TK2MSFTNGP09.phx.gbl...

quote:

---

> sanjay
> Can post DDL + sample data + expected result and then I can answer you

---

why

quote:

---

> it returns 9 rows and why it returns 34 rows.
>
>
> "sanjay" <anonymous@.discussions.microsoft.com> wrote in message
> news:60500162-DCCE-4D7A-9B3F-BE4A3C50B05C@.microsoft.com...
rows...i[QUOTE]
> think its using left join bcoz of that..
>

---

|||sanjay wrote:

quote:

---

> how to optimize the following queries..
> 1) SELECT * FROM employee
> WHERE job_id not IN ( SELECT job_id
> FROM jobs
> WHERE min_lvl = 25 )

---

1) keep it as it is
2) use EXISTS
SELECT *
FROM Employee
WHERE NOT EXISTS (
SELECT 1
FROM Jobs
WHERE Jobs.job_id = Employee.job_id
AND min_lvl=25
)
3) use OUTER JOIN
SELECT * -- Add DISTINCT if necessary
FROM Employee
LEFT JOIN Jobs
ON Jobs.job_id = Employee.job_id
AND Jobs.min_lvl = 25

quote:

---

> 2) select * from tbla where name like '%F'

---

ALTER TABLE tbla
ADD NameReverse varchar(8000) not null -- use same definition as column
Name
go
UPDATE tbla
SET NameReverse = REVERSE(Name)
go
CREATE INDEX IX_tbla_NameReverse ON tbla(NameReverse)
go
SELECT *
FROM tbla
WHERE NameReverse like REVERSE('F')+'%'
Hope this helps,
Gert-Jan|||Submitted too fast. Of course, the line "WHERE Jobs.job_id IS NULL"
needs to be added to solution 3...
Gert-Jan
Gert-Jan Strik wrote:

quote:

---

> sanjay wrote:
> 1) keep it as it is
> 2) use EXISTS
> SELECT *
> FROM Employee
> WHERE NOT EXISTS (
> SELECT 1
> FROM Jobs
> WHERE Jobs.job_id = Employee.job_id
> AND min_lvl=25
> )
> 3) use OUTER JOIN
> SELECT * -- Add DISTINCT if necessary
> FROM Employee
> LEFT JOIN Jobs
> ON Jobs.job_id = Employee.job_id
> AND Jobs.min_lvl = 25
>
> ALTER TABLE tbla
> ADD NameReverse varchar(8000) not null -- use same definition as column
> Name
> go
> UPDATE tbla
> SET NameReverse = REVERSE(Name)
> go
> CREATE INDEX IX_tbla_NameReverse ON tbla(NameReverse)
> go
> SELECT *
> FROM tbla
> WHERE NameReverse like REVERSE('F')+'%'
> Hope this helps,
> Gert-Jan

---

optimize queries.

how to optimize the following queries.
1) SELECT * FROM employe
WHERE job_id not IN ( SELECT job_i
FROM job
WHERE min_lvl = 25
2) select * from tbla where name like '%F
Regard
sanjaysanjay
It is to hard to say . You did not provide any info about how many data
exists in the table?
How often you query the table?
What is indexes on the table?
My guess.
1) SELECT * FROM employee
WHERE NOT EXISTS
( SELECT jobs .job_id
FROM jobs WHERE employee.job_id
=jobs.job_id AND min_lvl = 25 )
> 2) select * from tbla where name like '%F'
This one may prevent query optimyzer for using an index on the name column
unless you re-write it as 'F%'.
Consider to use covering indexes instead of select * from table .
For more details please refer to BOL
"sanjay" <anonymous@.discussions.microsoft.com> wrote in message
news:19EA7547-8EBC-43FF-9981-3AF635B34A03@.microsoft.com...
> how to optimize the following queries..
> 1) SELECT * FROM employee
> WHERE job_id not IN ( SELECT job_id
> FROM jobs
> WHERE min_lvl = 25 )
> 2) select * from tbla where name like '%F'
> Regards
> sanjay|||h
im using not In in my query..which as per documents/sqlserver etc..is not the best way to use and it hampers performance...how i can avoide not In.
execution plan of Not In/ Not exists are same.
suppost i ve 1 million records in this tables and its being heavily used.
both are normal tables from pUbs database.
=========2) if we rewrite the query as 'F%' this will change the logic of the query..keeping same logic how can we modify the query|||sanjay
1) Look , another approach but you will have to compare all methods .
1) SELECT * FROM employee E LEFT JOIN jobs J
ON E.job_id=J.job_id WHERE min_lvl = 25 AND J.job_id IS NOT NULL
2)
http://www.sql-server-performance.com/covering_indexes.asp
"sanjay" <anonymous@.discussions.microsoft.com> wrote in message
news:AFD7096E-56CF-4C1C-B71D-F4862A8EA60D@.microsoft.com...
> hi
> im using not In in my query..which as per documents/sqlserver etc..is not
the best way to use and it hampers performance...how i can avoide not In..
> execution plan of Not In/ Not exists are same..
> suppost i ve 1 million records in this tables and its being heavily used..
> both are normal tables from pUbs database..
> ==========> 2) if we rewrite the query as 'F%' this will change the logic of the
query..keeping same logic how can we modify the query.
>|||hi
i tried ..but seems some problem.
SELECT * FROM employee
WHERE job_id not IN ( SELECT job_id
FROM jobs
WHERE min_lvl = 25 )
the above query returned 34 rows...whereas the below query only 9 rows...i think its using left join bcoz of that..
SELECT * FROM employee E LEFT JOIN jobs J
ON E.job_id=J.job_id WHERE min_lvl = 25 AND J.job_id IS NOT NULL
any further updates would be highly appreciated..
regards|||sanjay
Can post DDL + sample data + expected result and then I can answer you why
it returns 9 rows and why it returns 34 rows.
"sanjay" <anonymous@.discussions.microsoft.com> wrote in message
news:60500162-DCCE-4D7A-9B3F-BE4A3C50B05C@.microsoft.com...
> hi
> i tried ..but seems some problem.
> SELECT * FROM employee
> WHERE job_id not IN ( SELECT job_id
> FROM jobs
> WHERE min_lvl = 25 )
> the above query returned 34 rows...whereas the below query only 9 rows...i
think its using left join bcoz of that..
> SELECT * FROM employee E LEFT JOIN jobs J
> ON E.job_id=J.job_id WHERE min_lvl = 25 AND J.job_id IS NOT NULL
> any further updates would be highly appreciated..
> regards
>|||Try below query :
select e.* from employee e, ( SELECT job_id
FROM jobs
WHERE min_lvl <> 25 ) a
where e.job_id = a.job_id
This would increase performance, as for each of the job_id, it wont scan
jobs table.
- Ketan
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OFu0osW6DHA.2460@.TK2MSFTNGP09.phx.gbl...
> sanjay
> Can post DDL + sample data + expected result and then I can answer you
why
> it returns 9 rows and why it returns 34 rows.
>
>
> "sanjay" <anonymous@.discussions.microsoft.com> wrote in message
> news:60500162-DCCE-4D7A-9B3F-BE4A3C50B05C@.microsoft.com...
> > hi
> > i tried ..but seems some problem.
> > SELECT * FROM employee
> > WHERE job_id not IN ( SELECT job_id
> > FROM jobs
> > WHERE min_lvl = 25 )
> >
> > the above query returned 34 rows...whereas the below query only 9
rows...i
> think its using left join bcoz of that..
> >
> > SELECT * FROM employee E LEFT JOIN jobs J
> > ON E.job_id=J.job_id WHERE min_lvl = 25 AND J.job_id IS NOT NULL
> >
> > any further updates would be highly appreciated..
> > regards
> >
>|||sanjay wrote:
> how to optimize the following queries..
> 1) SELECT * FROM employee
> WHERE job_id not IN ( SELECT job_id
> FROM jobs
> WHERE min_lvl = 25 )
1) keep it as it is
2) use EXISTS
SELECT *
FROM Employee
WHERE NOT EXISTS (
SELECT 1
FROM Jobs
WHERE Jobs.job_id = Employee.job_id
AND min_lvl=25
)
3) use OUTER JOIN
SELECT * -- Add DISTINCT if necessary
FROM Employee
LEFT JOIN Jobs
ON Jobs.job_id = Employee.job_id
AND Jobs.min_lvl = 25
> 2) select * from tbla where name like '%F'
ALTER TABLE tbla
ADD NameReverse varchar(8000) not null -- use same definition as column
Name
go
UPDATE tbla
SET NameReverse = REVERSE(Name)
go
CREATE INDEX IX_tbla_NameReverse ON tbla(NameReverse)
go
SELECT *
FROM tbla
WHERE NameReverse like REVERSE('F')+'%'
Hope this helps,
Gert-Jan|||Submitted too fast. Of course, the line "WHERE Jobs.job_id IS NULL"
needs to be added to solution 3...
Gert-Jan
Gert-Jan Strik wrote:
> sanjay wrote:
> >
> > how to optimize the following queries..
> > 1) SELECT * FROM employee
> > WHERE job_id not IN ( SELECT job_id
> > FROM jobs
> > WHERE min_lvl = 25 )
> 1) keep it as it is
> 2) use EXISTS
> SELECT *
> FROM Employee
> WHERE NOT EXISTS (
> SELECT 1
> FROM Jobs
> WHERE Jobs.job_id = Employee.job_id
> AND min_lvl=25
> )
> 3) use OUTER JOIN
> SELECT * -- Add DISTINCT if necessary
> FROM Employee
> LEFT JOIN Jobs
> ON Jobs.job_id = Employee.job_id
> AND Jobs.min_lvl = 25
> > 2) select * from tbla where name like '%F'
> ALTER TABLE tbla
> ADD NameReverse varchar(8000) not null -- use same definition as column
> Name
> go
> UPDATE tbla
> SET NameReverse = REVERSE(Name)
> go
> CREATE INDEX IX_tbla_NameReverse ON tbla(NameReverse)
> go
> SELECT *
> FROM tbla
> WHERE NameReverse like REVERSE('F')+'%'
> Hope this helps,
> Gert-Jansql

optimize queries with unexpected results

Hi,
This is one for the MVP’s. I am trying to optimize queries but I am findin
g
un-expected results. I am using sql 2000.
For example, I have read in several sites that referencing objects with
qualified owner names is faster. The truth is that I am experiencing all the
contrary. Am I doing something wrong in my test or this is just a rumour and
no objects should be qualified?
I am running both next queries in my own desktop machine, and clearing the
chache every time I run the query as per below scripts . As I understand tha
t
the workload in my machine might vary (not really doing nothing during the
test) I am taking at least 10 runs of each script and take the average
results.
I would expect better results for script 2 that for one (test show the
contrary), can anybody explain why? I am examining several counters, but
would be happy enough if just logical reads would show better results. With
this results … how can I face the developers and tell them this and other
supposed coding “advantages”, or shall I just forget about reality and t
amper
the results in a Mendel fashion :-) ?
Script1:
SET STATISTICS IO on
SET STATISTICS TIME on
--above to lines just run once in each window
DBCC DROPCLEANBUFFERS
dbcc freeproccache
SELECT TOP 1000 *
FROM Table1 , Table2
where Table1.Idfield= Table2.Idfield
Script2:
SET STATISTICS IO on
SET STATISTICS TIME on
--above to lines just run once in each window
DBCC DROPCLEANBUFFERS
dbcc freeproccache
SELECT TOP 1000 *
FROM dbo.Table1 , dbo.Table2
where dbo.Table1.Idfield= dbo.Table2.Idfield
Thanks in advance, your help is much appreciated,
TristanIn addition to the [procedure cache] which contains execution plans,
there is also the [buffer cache] which contains previously read data and
index pages. This can have an even greater impact on query duration,
especially if you are executing the same query back to back. Since this is a
local instance of SQL Server, you may want to stop / re-start the service
prior to each test.
http://msdn.microsoft.com/library/d...br />
1zu4.asp
Also, in Query Analyzer, choose the menu option Query.. Show Execution
Plan. After the query comples, hovering the mouse over each step will reveal
information like whether a index s, index scan, table scan, etc. were
performed and the actual I/O and CPU cost. If (after completely clearing all
caches) one run has basically the same I./O cost as another run, then there
are probably other environmental factors that influenced the outcome.
Performance on a desktop PC will be more variable than on a production
server, becuase there are so many other background services and processes,
such as anti-virus, running.
"Tristan" <Tristan@.discussions.microsoft.com> wrote in message
news:1D180C57-9AFF-458D-9529-9560FB154B73@.microsoft.com...
> Hi,
> This is one for the MVP's. I am trying to optimize queries but I am
> finding
> un-expected results. I am using sql 2000.
> For example, I have read in several sites that referencing objects with
> qualified owner names is faster. The truth is that I am experiencing all
> the
> contrary. Am I doing something wrong in my test or this is just a rumour
> and
> no objects should be qualified?
> I am running both next queries in my own desktop machine, and clearing the
> chache every time I run the query as per below scripts . As I understand
> that
> the workload in my machine might vary (not really doing nothing during the
> test) I am taking at least 10 runs of each script and take the average
> results.
> I would expect better results for script 2 that for one (test show the
> contrary), can anybody explain why? I am examining several counters, but
> would be happy enough if just logical reads would show better results.
> With
> this results . how can I face the developers and tell them this and other
> supposed coding "advantages", or shall I just forget about reality and
> tamper
> the results in a Mendel fashion :-) ?
> Script1:
> SET STATISTICS IO on
> SET STATISTICS TIME on
> --above to lines just run once in each window
> DBCC DROPCLEANBUFFERS
> dbcc freeproccache
> SELECT TOP 1000 *
> FROM Table1 , Table2
> where Table1.Idfield= Table2.Idfield
> Script2:
> SET STATISTICS IO on
> SET STATISTICS TIME on
> --above to lines just run once in each window
> DBCC DROPCLEANBUFFERS
> dbcc freeproccache
> SELECT TOP 1000 *
> FROM dbo.Table1 , dbo.Table2
> where dbo.Table1.Idfield= dbo.Table2.Idfield
> Thanks in advance, your help is much appreciated,
> Tristan
>|||The first thing you should do is to look at the query plan for each of these
scenarios and see what the difference is..
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Tristan" wrote:

> Hi,
> This is one for the MVP’s. I am trying to optimize queries but I am find
ing
> un-expected results. I am using sql 2000.
> For example, I have read in several sites that referencing objects with
> qualified owner names is faster. The truth is that I am experiencing all t
he
> contrary. Am I doing something wrong in my test or this is just a rumour a
nd
> no objects should be qualified?
> I am running both next queries in my own desktop machine, and clearing the
> chache every time I run the query as per below scripts . As I understand t
hat
> the workload in my machine might vary (not really doing nothing during the
> test) I am taking at least 10 runs of each script and take the average
> results.
> I would expect better results for script 2 that for one (test show the
> contrary), can anybody explain why? I am examining several counters, but
> would be happy enough if just logical reads would show better results. Wit
h
> this results … how can I face the developers and tell them this and othe
r
> supposed coding “advantages”, or shall I just forget about reality and
tamper
> the results in a Mendel fashion :-) ?
> Script1:
> SET STATISTICS IO on
> SET STATISTICS TIME on
> --above to lines just run once in each window
> DBCC DROPCLEANBUFFERS
> dbcc freeproccache
> SELECT TOP 1000 *
> FROM Table1 , Table2
> where Table1.Idfield= Table2.Idfield
> Script2:
> SET STATISTICS IO on
> SET STATISTICS TIME on
> --above to lines just run once in each window
> DBCC DROPCLEANBUFFERS
> dbcc freeproccache
> SELECT TOP 1000 *
> FROM dbo.Table1 , dbo.Table2
> where dbo.Table1.Idfield= dbo.Table2.Idfield
> Thanks in advance, your help is much appreciated,
> Tristan
>|||Hi JT / Wayne,
I was allready looking with execution plans plans and where the exactly the
same, statistics showed brutal differences, but the worst was with the logia
l
reads, it just didnt make sense a diference of 1000 between one method and
the other. As sometimes happends in this job guys, closed the window and
opened another one and the error disapeared.
Anyway, thanks a lot guys.
Hi JT, FYI from BOL "Use DBCC DROPCLEANBUFFERS to test queries with a cold
buffer cache without shutting down and restarting the server.", dont know if
there are any other methods to clear more buffer than the ones I ussed,
though u r right to advise not to test in PC, sorry but prod and UAT are
fried with users :-)
"JT" wrote:

> In addition to the [procedure cache] which contains execution plans,
> there is also the [buffer cache] which contains previously read data and
> index pages. This can have an even greater impact on query duration,
> especially if you are executing the same query back to back. Since this is
a
> local instance of SQL Server, you may want to stop / re-start the service
> prior to each test.
> http://msdn.microsoft.com/library/d... />
a_1zu4.asp
> Also, in Query Analyzer, choose the menu option Query.. Show Execution
> Plan. After the query comples, hovering the mouse over each step will reve
al
> information like whether a index s, index scan, table scan, etc. were
> performed and the actual I/O and CPU cost. If (after completely clearing a
ll
> caches) one run has basically the same I./O cost as another run, then ther
e
> are probably other environmental factors that influenced the outcome.
> Performance on a desktop PC will be more variable than on a production
> server, becuase there are so many other background services and processes,
> such as anti-virus, running.
>
>
> "Tristan" <Tristan@.discussions.microsoft.com> wrote in message
> news:1D180C57-9AFF-458D-9529-9560FB154B73@.microsoft.com...
>
>|||There can be probably "wrong" index use problem, look ath te query analyzer,
which index is really used in
query and probably you must force the
select INDEX hint
Wayne Snyder wrote:
>The first thing you should do is to look at the query plan for each of thes
e
>scenarios and see what the difference is..
>
>[quoted text clipped - 45 lines]
Message posted via http://www.webservertalk.com|||Tristan,
The execution plans of the two queries should be the same. If they are
not, then that is a different issue.
If you use TOP 1000 and the join between Table1 and Table2 produces more
than 1000 rows, then the results can differ between runs. That is not a
good test situation.
The difference between a qualified and unqualified object is only
relevant in the query compilation phase. The potential performance
difference will not show up in the logical reads.
Gert-Jan
Tristan wrote:
> Hi,
> This is one for the MVP’s. I am trying to optimize queries but I am find
ing
> un-expected results. I am using sql 2000.
> For example, I have read in several sites that referencing objects with
> qualified owner names is faster. The truth is that I am experiencing all t
he
> contrary. Am I doing something wrong in my test or this is just a rumour a
nd
> no objects should be qualified?
> I am running both next queries in my own desktop machine, and clearing the
> chache every time I run the query as per below scripts . As I understand t
hat
> the workload in my machine might vary (not really doing nothing during the
> test) I am taking at least 10 runs of each script and take the average
> results.
> I would expect better results for script 2 that for one (test show the
> contrary), can anybody explain why? I am examining several counters, but
> would be happy enough if just logical reads would show better results. Wit
h
> this results … how can I face the developers and tell them this and othe
r
> supposed coding “advantages”, or shall I just forget about reality and
tamper
> the results in a Mendel fashion :-) ?
> Script1:
> SET STATISTICS IO on
> SET STATISTICS TIME on
> --above to lines just run once in each window
> DBCC DROPCLEANBUFFERS
> dbcc freeproccache
> SELECT TOP 1000 *
> FROM Table1 , Table2
> where Table1.Idfield= Table2.Idfield
> Script2:
> SET STATISTICS IO on
> SET STATISTICS TIME on
> --above to lines just run once in each window
> DBCC DROPCLEANBUFFERS
> dbcc freeproccache
> SELECT TOP 1000 *
> FROM dbo.Table1 , dbo.Table2
> where dbo.Table1.Idfield= dbo.Table2.Idfield
> Thanks in advance, your help is much appreciated,
> Tristan

Optimize queries

Hi everyone,
We have a SQL-Server 2000 DB with Full-Text-Search enabled on some of the
tables.
We have one table with about 15 million records. The table looks like
Please repost - something seems to be missing from your post.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Yair Nissan" <yair@.yairnis.com> wrote in message
news:urj0O0GOGHA.2336@.TK2MSFTNGP12.phx.gbl...
> Hi everyone,
> We have a SQL-Server 2000 DB with Full-Text-Search enabled on some of the
> tables.
> We have one table with about 15 million records. The table looks like
>

Optimization

I was asked to find out if there is any difference between the two queries
for performance.
Example 1:
Server: ServerProd
Database: DB1
Query: Select * from Table1
Example 2:
Server: ServerProd
Database: DB2
Query: Select * from DB1..Table1
Does anybody know?
AngieThere is no difference. See with the query plan.
"Angela Shinkle" <ashinkle@.aspenmed.org> wrote in message
news:OhdCXAXpDHA.2772@.TK2MSFTNGP10.phx.gbl...
> I was asked to find out if there is any difference between the two queries
> for performance.
> Example 1:
> Server: ServerProd
> Database: DB1
> Query: Select * from Table1
>
> Example 2:
> Server: ServerProd
> Database: DB2
> Query: Select * from DB1..Table1
>
> Does anybody know?
> Angie
>|||no comment on this, but if the user and owner are
different, it will help to specify the table owner
>--Original Message--
>I was asked to find out if there is any difference
between the two queries
>for performance.
>Example 1:
> Server: ServerProd
> Database: DB1
> Query: Select * from Table1
>
>Example 2:
> Server: ServerProd
> Database: DB2
> Query: Select * from
DB1..Table1
>
>Does anybody know?
>Angie
>
>.
>|||If anything I would think the DB1..Table1 would be slower, however,
specifying the ownername for your table stops SQL from having to search on
your username to see if you have a table name that, so select * from
dbo.table1 is slightly faster than select * from table1 as SQL would go out
and see if there were any tables named yourusername.table1 and if not, then
select dbo.table1
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Angela Shinkle" <ashinkle@.aspenmed.org> wrote in message
news:OhdCXAXpDHA.2772@.TK2MSFTNGP10.phx.gbl...
> I was asked to find out if there is any difference between the two queries
> for performance.
> Example 1:
> Server: ServerProd
> Database: DB1
> Query: Select * from Table1
>
> Example 2:
> Server: ServerProd
> Database: DB2
> Query: Select * from DB1..Table1
>
> Does anybody know?
> Angie
>|||Yep, I did some tests a while ago.
dbo.tblname is the fastest.
Including database name is slightly slower, even if you already are in the
correct database.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Ray Higdon" <rayhigdon@.higdonconsulting.com> wrote in message
news:OTjHfnYpDHA.2500@.TK2MSFTNGP10.phx.gbl...
> If anything I would think the DB1..Table1 would be slower, however,
> specifying the ownername for your table stops SQL from having to search on
> your username to see if you have a table name that, so select * from
> dbo.table1 is slightly faster than select * from table1 as SQL would go
out
> and see if there were any tables named yourusername.table1 and if not,
then
> select dbo.table1
> HTH
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "Angela Shinkle" <ashinkle@.aspenmed.org> wrote in message
> news:OhdCXAXpDHA.2772@.TK2MSFTNGP10.phx.gbl...
> > I was asked to find out if there is any difference between the two
queries
> > for performance.
> >
> > Example 1:
> > Server: ServerProd
> > Database: DB1
> > Query: Select * from Table1
> >
> >
> > Example 2:
> > Server: ServerProd
> > Database: DB2
> > Query: Select * from DB1..Table1
> >
> >
> > Does anybody know?
> >
> > Angie
> >
> >
>

Optimising queries

Hi.

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

I know it looks simple, but so am I.

Cheers

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

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

It's unclear to me what

(barney>rubble and fred<flintoff)

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

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

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

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

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

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

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

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

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

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

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

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

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|||for help looking at creating indexes, look at the where clauses in your
select statements.

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

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

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

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

Optimise these 3 queries into one?

I have these 3 queries - they are the same except each fetches record counts
for one of 3 different record types, nSubsets (type 0), nAssets (type 1) and
nImages (type 2). Is there any way I could get all 3 of these (based on the
Node.Type integer) with a single query?

IF @.Error = 0
BEGIN
SELECT @.nSubsets = COUNT(*)
FROM Node
INNER JOIN Adjacency
ON Adjacency.ID_Node = Node.ID
WHERE Adjacency.Path LIKE @.nodepath + '%'
AND
Node.Type = 0

SET @.Error = @.ERROR
END

IF @.Error = 0
BEGIN
SELECT @.nAssets = COUNT(*)
FROM Node
INNER JOIN Adjacency
ON Adjacency.ID_Node = Node.ID
WHERE Adjacency.Path LIKE @.nodepath + '%'
AND
Node.Type = 1

SET @.Error = @.ERROR
END

IF @.Error = 0
BEGIN
SELECT @.nImages = COUNT(*)
FROM Node
INNER JOIN Adjacency
ON Adjacency.ID_Node = Node.ID
WHERE Adjacency.Path LIKE @.nodepath + '%'
AND
Node.Type = 2

SET @.Error = @.ERROR
ENDSELECT
@.nSubsets = COUNT(CASE Node.type WHEN 0 THEN 1 END),
@.nAssets = COUNT(CASE Node.type WHEN 1 THEN 1 END),
@.nImages = COUNT(CASE Node.type WHEN 2 THEN 1 END)
FROM Node
INNER JOIN Adjacency
ON Adjacency.ID_Node = Node.ID
WHERE Adjacency.Path LIKE @.nodepath + '%'

--
David Portas
----
Please reply only to the newsgroup
--|||Robin,

I'm assuming you wanted the result in one row. This is untested, but
you get the idea...

IF @.Error = 0
BEGIN
SELECT SUM (CASE Node.Type
WHEN 0 THEN 1
ELSE 0
END) AS nSubSets_cnt
,SUM (CASE Node.Type
WHEN 1 THEN 1
ELSE 0
END) AS nAssets_cnt
,SUM (CASE Node.Type
WHEN 2 THEN 1
ELSE 0
END) AS nImages_cnt
FROM Node
INNER JOIN
Adjacency
ON Adjacency.ID_Node = Node.ID
WHERE Adjacency.Path LIKE @.nodepath + '%'
AND Node.Type = in (0, 1, 2)

SET @.Error = @.ERROR
END

Christian.|||How about this?

SELECT @.nSubsets = sum(case when node.type = 0 then 1 else 0 end),
@.nAssets = sum(case when node.type = 1 then 1 else 0 end),
@.nImages = sum(case when node.type = 2 then 1 else 0 end),
FROM Node
INNER JOIN Adjacency
ON Adjacency.ID_Node = Node.ID
WHERE Adjacency.Path LIKE @.nodepath + '%'

I think tyhis will get you what you are looking for. I haven't run it
against anything, but I'm fairly sure it's good to go.

Hope it works for you,
Carl

"Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in message news:<bs9jhc$gfo$1$8300dec7@.news.demon.co.uk>...
> I have these 3 queries - they are the same except each fetches record counts
> for one of 3 different record types, nSubsets (type 0), nAssets (type 1) and
> nImages (type 2). Is there any way I could get all 3 of these (based on the
> Node.Type integer) with a single query?
> IF @.Error = 0
> BEGIN
> SELECT @.nSubsets = COUNT(*)
> FROM Node
> INNER JOIN Adjacency
> ON Adjacency.ID_Node = Node.ID
> WHERE Adjacency.Path LIKE @.nodepath + '%'
> AND
> Node.Type = 0
> SET @.Error = @.ERROR
> END
> IF @.Error = 0
> BEGIN
> SELECT @.nAssets = COUNT(*)
> FROM Node
> INNER JOIN Adjacency
> ON Adjacency.ID_Node = Node.ID
> WHERE Adjacency.Path LIKE @.nodepath + '%'
> AND
> Node.Type = 1
> SET @.Error = @.ERROR
> END
> IF @.Error = 0
> BEGIN
> SELECT @.nImages = COUNT(*)
> FROM Node
> INNER JOIN Adjacency
> ON Adjacency.ID_Node = Node.ID
> WHERE Adjacency.Path LIKE @.nodepath + '%'
> AND
> Node.Type = 2
> SET @.Error = @.ERROR
> END|||Use the CASE function.
EG:

select

CASE when Adjacency.Path LIKE @.nodepath + '%' AND Node.Type = 0 then 1
else 0 end "NT=0",
CASE when Adjacency.Path LIKE @.nodepath + '%' AND Node.Type = 1 then 1
else 0 end "NT=1",
CASE when Adjacency.Path LIKE @.nodepath + '%' AND Node.Type = 2 then 1
else 0 end "NT=2",

from ...

"Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:bs9jhc$gfo$1$8300dec7@.news.demon.co.uk...
> I have these 3 queries - they are the same except each fetches record
counts
> for one of 3 different record types, nSubsets (type 0), nAssets (type 1)
and
> nImages (type 2). Is there any way I could get all 3 of these (based on
the
> Node.Type integer) with a single query?
> IF @.Error = 0
> BEGIN
> SELECT @.nSubsets = COUNT(*)
> FROM Node
> INNER JOIN Adjacency
> ON Adjacency.ID_Node = Node.ID
> WHERE Adjacency.Path LIKE @.nodepath + '%'
> AND
> Node.Type = 0
> SET @.Error = @.ERROR
> END
> IF @.Error = 0
> BEGIN
> SELECT @.nAssets = COUNT(*)
> FROM Node
> INNER JOIN Adjacency
> ON Adjacency.ID_Node = Node.ID
> WHERE Adjacency.Path LIKE @.nodepath + '%'
> AND
> Node.Type = 1
> SET @.Error = @.ERROR
> END
> IF @.Error = 0
> BEGIN
> SELECT @.nImages = COUNT(*)
> FROM Node
> INNER JOIN Adjacency
> ON Adjacency.ID_Node = Node.ID
> WHERE Adjacency.Path LIKE @.nodepath + '%'
> AND
> Node.Type = 2
> SET @.Error = @.ERROR
> END|||All good stuff. Thanks for all 3 replys.

"Carl Reeds" <creeds0001@.msn.com> wrote in message
news:956eb1fa.0312231411.4e7cec31@.posting.google.c om...
> How about this?
> SELECT @.nSubsets = sum(case when node.type = 0 then 1 else 0 end),
> @.nAssets = sum(case when node.type = 1 then 1 else 0 end),
> @.nImages = sum(case when node.type = 2 then 1 else 0 end),
> FROM Node
> INNER JOIN Adjacency
> ON Adjacency.ID_Node = Node.ID
> WHERE Adjacency.Path LIKE @.nodepath + '%'
> I think tyhis will get you what you are looking for. I haven't run it
> against anything, but I'm fairly sure it's good to go.
> Hope it works for you,
> Carl
>
> "Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:<bs9jhc$gfo$1$8300dec7@.news.demon.co.uk>...
> > I have these 3 queries - they are the same except each fetches record
counts
> > for one of 3 different record types, nSubsets (type 0), nAssets (type 1)
and
> > nImages (type 2). Is there any way I could get all 3 of these (based on
the
> > Node.Type integer) with a single query?
> > IF @.Error = 0
> > BEGIN
> > SELECT @.nSubsets = COUNT(*)
> > FROM Node
> > INNER JOIN Adjacency
> > ON Adjacency.ID_Node = Node.ID
> > WHERE Adjacency.Path LIKE @.nodepath + '%'
> > AND
> > Node.Type = 0
> > SET @.Error = @.ERROR
> > END
> > IF @.Error = 0
> > BEGIN
> > SELECT @.nAssets = COUNT(*)
> > FROM Node
> > INNER JOIN Adjacency
> > ON Adjacency.ID_Node = Node.ID
> > WHERE Adjacency.Path LIKE @.nodepath + '%'
> > AND
> > Node.Type = 1
> > SET @.Error = @.ERROR
> > END
> > IF @.Error = 0
> > BEGIN
> > SELECT @.nImages = COUNT(*)
> > FROM Node
> > INNER JOIN Adjacency
> > ON Adjacency.ID_Node = Node.ID
> > WHERE Adjacency.Path LIKE @.nodepath + '%'
> > AND
> > Node.Type = 2
> > SET @.Error = @.ERROR
> > END

OPENXML Question

I have an text column in a table that was intended to store XML data about queries that users submitted in our database. The problem appears that the application inserting records into the table doesn't do a great job of formatting the XML and I am having a great deal of trouble getting workable results.

The XML data in the table looks like:
<request>
<name>LastName</name>
<oper>=</oper>
<value>Smith</value>
<name>FirstName</name>
<oper>=</oper>
<value>John</value>
<name>MiddleName</name>
<oper>=</oper>
<value>Q.</value>
</request>

Note the groups of three elements (name, oper, value). These always appear sequentially in the data and there are always a fixed number of elements. If an element has no data, then it contains the value 'null' (ie, <value>null</value>).

I want to get a result like:

Col1 Col2 Col3
======== ====== ========
LastName = Smith
FirstName = John
MiddleName = Q

When I execute OPENXML, I specify option 2 (element centric) and the following WITH clause:

WITH (name varchar(10),
oper varchar(10),
value varchar(50) )

I get a single row returned. I have also tried:

WITH (name varchar(10) '@.name',
oper varchar(10) '@.oper',
value varchar(50) '@.value')

I get a single row returned (with null values).

I've tried lots of other permutations, but nothing so far has worked. The only thing that has been modestly successful is to create and Edge Table (exclude the WITH clause), but that's really hard for me to work with. Another option is to insert a <line num="#">...<line> around each group of three elements (name, oper, value). That worked well, but it's a pain to implement (it means going back and updating all rows where the XML data exists and inserting the proper data.

I am by no means an XML guru, so if anyone has a suggestion, I'd love to hear it.

Regards,

hmscottI think you are struggling because you have assigned meaning to the order of elements in your source xml file. To paraphrase Brett: the order of elements in an xml file *should* have no meaning. Assigning meaning to the order of elements leads to fragile and inflexible code (dependent on element order to work correctly).

a better xml design would be something that groups each tuple, but (as you mention above) that means you have to change the xml.

For instance:

<request>
<var name="LastName" oper="=" value="Smith"/>
...
</request>

so that it's unambiguous which name/oper/value goes together.|||I think you are struggling because you have assigned meaning to the order of elements in your source xml file. To paraphrase Brett: the order of elements in an xml file *should* have no meaning. Assigning meaning to the order of elements leads to fragile and inflexible code (dependent on element order to work correctly).

I didn't assign meaning to the order of anything. This is legacy code that's been around since before my time (and which also means that the developer whose neck I wish to wring has long since departed).

I know what I would do to prevent the problem; what can I do to deal with the data as it is (about 1,000,000+ rows)?

Regards,

hmscott|||well, this works, but I'm not terribly proud of it. It's certainly fragile... :)

declare @.names table (id int identity(1,1), name varchar(10))
declare @.opers table (id int identity(1,1), oper varchar(10))
declare @.values table (id int identity(1,1), value varchar(10))

DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc ='
<request>
<name>LastName</name>
<oper>=</oper>
<value>Smith</value>
<name>FirstName</name>
<oper>=</oper>
<value>John</value>
<name>MiddleName</name>
<oper>=</oper>
<value>Q.</value>
</request>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc

insert into @.names (name) SELECT * FROM OPENXML (@.idoc, '/request/name',1) WITH (name varchar(10) '.')
insert into @.opers (oper) SELECT * FROM OPENXML (@.idoc, '/request/oper',1) WITH (oper varchar(10) '.')
insert into @.values (value) SELECT * FROM OPENXML (@.idoc, '/request/value',1) WITH (value varchar(10) '.')

EXEC sp_xml_removedocument @.idoc

select n.name, o.oper, v.value from
@.names n
join @.opers o on o.id=n.id
join @.values v on v.id=n.id|||well, this works, but I'm not terribly proud of it. It's certainly fragile... :)

Hey, thanks. I hadn't thought of trying it that way.
:beer:

That's what I get for beating my head against the wall for too long.

Regards,

hmscott|||glad to help. note that my idea falls down completely if there is a missing name,oper, or value element anywhere in your file, because then it would group the wrong elements together.

that's what I meant by it being fragile. :)|||glad to help. note that my idea falls down completely if there is a missing name,oper, or value element anywhere in your file, because then it would group the wrong elements together.

that's what I meant by it being fragile. :)

That's understood. Actually, the way the data is organized, I think I can throw this inside a cursor. It still will fail in the event of a missing element, but perhaps a bit of error checking will capture that.

Thanks again.

hmscott