Performance Optimization

I have one problem while optimizing the sql query .For a few rows the
query works perfectly ,but as the number of rows increases it works but
gives wrong result.
I am using nested queries W/O aliasing . So what I assume is that Query
Optimizer is trying to flatten the query (converting it into joins) and
in the process ,because of no Alias Name takes a long time .
Something like this :--
Select * From EmpMst Where deptid in
( Select deptid from deptmst where deptname='acc')
--
into This --
--
Select * From EmpMst EM
Inner Join DeptMst DM On Dm.deptid = EM.deptid and DM.deptname='acc'
What might be the reasons for performance debacle? Following is what I
am using
1. Views (using *) --
2. Indexing (Clustered)
3. History Data (2 Billion Rows)
4. No Indexed views
5. Scalar Functions ( a bit For format Checking like All
alphabets,digits etc)
6. No Cursor
7. Updating a permanent temp table for intermediate results.
With Warm regards
Jatinder SinghIt is not clear to me what your question is? Is your question about
performance, or about getting a queries that always returns correct
results?
Which query are you currently using? The first or the second?
If you replace "*" with "EM.*" in the second query, then it is still not
(necessarily) equivalent to the first. If table DeptMst contains
duplicate deptid values, then the second query will return more rows
than the first. This effect can be cancelled out by adding the DISTINCT
keyword (and selection only from the EmpMst table).
Also, if column DeptMst.deptid contains NULLs, then the queries will
return different results.
If you are using SQL2K SP4, and the queries does not perform well, then
maybe you don't have proper indexes in place, or maybe your statistics
are not up to date. You did not post any DDL, so you indexing strategy
is unknown to us.
Gert-Jan
jsfromynr wrote:
> I have one problem while optimizing the sql query .For a few rows the
> query works perfectly ,but as the number of rows increases it works but
> gives wrong result.
> I am using nested queries W/O aliasing . So what I assume is that Query
> Optimizer is trying to flatten the query (converting it into joins) and
> in the process ,because of no Alias Name takes a long time .
> Something like this :--
> Select * From EmpMst Where deptid in
> ( Select deptid from deptmst where deptname='acc')
> --
> into This --
> --
> Select * From EmpMst EM
> Inner Join DeptMst DM On Dm.deptid = EM.deptid and DM.deptname='acc'
> What might be the reasons for performance debacle? Following is what I
> am using
> 1. Views (using *) --
> 2. Indexing (Clustered)
> 3. History Data (2 Billion Rows)
> 4. No Indexed views
> 5. Scalar Functions ( a bit For format Checking like All
> alphabets,digits etc)
> 6. No Cursor
> 7. Updating a permanent temp table for intermediate results.
> With Warm regards
> Jatinder Singh|||Hi There,
Sorry for not making myself clear . I know you can solve / suggest
some new points.
I am using First Query.
The example I had taken is where empid is PK in EmpMst and deptid is PK
in DeptMst so they cannot be null.
(if column DeptMst.deptid contains NULLs, then the queries will return
different results.
And If table DeptMst contains duplicate deptid values, then the second
query will return more rows than the first)
Select * From EmpMst Where deptid in
( Select deptid from deptmst where deptname='acc')
--
into This --
--
Select * From EmpMst EM
Inner Join DeptMst DM On Dm.deptid = EM.deptid and DM.deptname='acc'
That is the way I thought (I can be wrong) Query Optimizer will Convert
the queries with IN (Subquery...)
What might be the reasons for performance debacle (in Case I am using
Subqueries/Corelated Subqueries)?I assume here that Subqueries are
executed using tempdb.
Following is what I am using
1. Views (using *) --
2. Indexing (Clustered)
3. History Data (2 Billion Rows)
4. No Indexed views
5. Scalar Functions ( a bit For format Checking like All
alphabets,digits etc)
6. No Cursor
7. Updating a permanent temp table for intermediate results.
With Warm regards
Jatinder Singh|||You can look at the execution plan to see if the optimizer transforms
the query the way you describe. Press CTRL+G in Query Analyser and then
run the query to see the execution plan.
How the optimizer will execute your query depends on the relative table
sizes, the selectivity of the column you join on, and the 'width' of the
available indexes.
In this case the optimizer can choose between three strategies:
1a) use EmpMst as outer table and perform lookups for each relevant
deptid in DeptMst
1b) use DeptMst as outer table and perform lookups for each relevant
deptid in EmpMst (provided that you have an index on EmpMst(deptid))
(unlikely in your case)
2) scan both tables and join the result with the hashing algorithm
3) use a merge join to simulateneously scan both tables and join them
(unlikely in your case)
Under normal circumstances it is not necessary to manually rewrite the
query, because the optimizer will figure out the best plan.
However, if DeptMst and/or EmpMst are views, then no one can tell
anything about it, because you haven't posted any view definition, or
any DDL, indexes, etc. This is important information for performance
related questions. All 7 point you mention can affect performance, but
without addition information...
Maybe you can find some useful information on
http://www.sql-server-performance.com/
If not, then please provide more information (see
http://www.aspfaq.com/etiquette.asp?id=5006)
Gert-Jan
jsfromynr wrote:
> Hi There,
> Sorry for not making myself clear . I know you can solve / suggest
> some new points.
> I am using First Query.
> The example I had taken is where empid is PK in EmpMst and deptid is PK
> in DeptMst so they cannot be null.
> (if column DeptMst.deptid contains NULLs, then the queries will return
> different results.
> And If table DeptMst contains duplicate deptid values, then the second
> query will return more rows than the first)
> Select * From EmpMst Where deptid in
> ( Select deptid from deptmst where deptname='acc')
> --
> into This --
> --
> Select * From EmpMst EM
> Inner Join DeptMst DM On Dm.deptid = EM.deptid and DM.deptname='acc'
> That is the way I thought (I can be wrong) Query Optimizer will Convert
> the queries with IN (Subquery...)
> What might be the reasons for performance debacle (in Case I am using
> Subqueries/Corelated Subqueries)?I assume here that Subqueries are
> executed using tempdb.
> Following is what I am using
> 1. Views (using *) --
> 2. Indexing (Clustered)
> 3. History Data (2 Billion Rows)
> 4. No Indexed views
> 5. Scalar Functions ( a bit For format Checking like All
> alphabets,digits etc)
> 6. No Cursor
> 7. Updating a permanent temp table for intermediate results.
> With Warm regards
> Jatinder Singh|||Hi There,
Thanks for your input. The database design you are asking for is too
big to post . Your explaination is excellent. I should say with minimal
information ,you gave me geniune help. I hope someday , I will be
helping others as you do.
Yeah www.sql-server-performance.com is the site I regulary visit , not
to forget dbazine.com
I wish , I could learn how to flatten the correlated subqueries ; The
cursor based mindset push me there . Do you know some reference
articles / sites which can provide help on this topic ( Performance ).
Thanks Again.
With Warm regards
Jatinder Singh

Performance of Views

I'm currently using a system where the number of column in any given table is so great that the columns are often split into additonal tables. I know it's a wierd design but there you go. So I have to deal with tables looking like:
MathResult, MathResult_2, MathResult_3, etc
Each table is basically the same entity, i.e. it has the same number of rows and each row has the same key value as its peer tables.
My question is that should I create a view to bring the tables together, given that a View doesn't seem to have any sort of row-size restriction? Normally I shy away from Views because I've always found them to bring performance down. Any thoughts?EDIT
Views are query rewrites so you will get mathematical problems like ISNULL function giving just plain wrong numbers. That said you can combine all into a UNION ALL view. This article is old but read what RDBMS(relational database management system) vendor agnostic expert Craig Mullins says about SQL Server Views in 1999.
I forgot to add that views don't use any query optimization plans and usually ignore the plans even if you create one. The reason persisted queries. Hope this helps.
http://www.craigsmullins.com/cnr_0299b.htm|||Thanks for the reply, yes the artical is a bit out-dated but IMO the basic points are still true. The trouble I'm having is that I know the views will be bad but I'm finding it difficult to quantify it, "how much worse will a view be"?|||

I would think that indexed Views will give you acceptable performance but I have not run the tests so I cannot be sure. The person who run some tests recently for all RDBMS(relational database management systems) for an advanced SQL book was Peter Gulutzan of now MySQL. I have the link to his recent article and a SQL Server specific Views performance article both should give you the dos and don't. Hope this helps.
http://www.dbazine.com/db2/db2-disarticles/gulutzan9

http://www.informit.com/articles/article.asp?p=130855

|||

Om Sri Sai Ram

I think indexed views effects performance. For every row insertion in base table, one record needs to be inserted for indexed view

Thanks,

Ram

Performance of MSDE Vs. SQL Server 2000

Hi there. I understand that MSDE has limitation such as 25 concurrent
users. However, under the same number of concurrent user environment
(say, 1 user) and hardware, is it possible that there will be much
performance difference between MSDE and SQL Server 2000?
What if MSDE runs under XP Pro and SQL SErver 2000 under Windows 2000?
Thanks
Dom<domtam@.hotmail.com> wrote in message
news:1130287729.767464.93730@.g43g2000cwa.googlegroups.com...
> Hi there. I understand that MSDE has limitation such as 25 concurrent
> users. However, under the same number of concurrent user environment
> (say, 1 user) and hardware, is it possible that there will be much
> performance difference between MSDE and SQL Server 2000?
> What if MSDE runs under XP Pro and SQL SErver 2000 under Windows 2000?
>
MSDE has no limit on concurrent users. It has a workload governer which
slows down processing when there are more than 5 concurrent workloads (not
users or connections). In addition databases are limited to 2GB each.
Within the limitations of MSDE the performance should be similar.
The replacement for MSDE, SQL Server 2005 Express Edition has no governor or
user limitation, but is limited to using 1GB of ram, 1 processor and
databases are limited to 4GB each.
David

Performance of MSDE Vs. SQL Server 2000

Hi there. I understand that MSDE has limitation such as 25 concurrent
users. However, under the same number of concurrent user environment
(say, 1 user) and hardware, is it possible that there will be much
performance difference between MSDE and SQL Server 2000?
What if MSDE runs under XP Pro and SQL SErver 2000 under Windows 2000?
Thanks
Dom
<domtam@.hotmail.com> wrote in message
news:1130287729.767464.93730@.g43g2000cwa.googlegro ups.com...
> Hi there. I understand that MSDE has limitation such as 25 concurrent
> users. However, under the same number of concurrent user environment
> (say, 1 user) and hardware, is it possible that there will be much
> performance difference between MSDE and SQL Server 2000?
> What if MSDE runs under XP Pro and SQL SErver 2000 under Windows 2000?
>
MSDE has no limit on concurrent users. It has a workload governer which
slows down processing when there are more than 5 concurrent workloads (not
users or connections). In addition databases are limited to 2GB each.
Within the limitations of MSDE the performance should be similar.
The replacement for MSDE, SQL Server 2005 Express Edition has no governor or
user limitation, but is limited to using 1GB of ram, 1 processor and
databases are limited to 4GB each.
David

Performance of MSDE Vs. SQL Server 2000

Hi there. I understand that MSDE has limitation such as 25 concurrent
users. However, under the same number of concurrent user environment
(say, 1 user) and hardware, is it possible that there will be much
performance difference between MSDE and SQL Server 2000?
What if MSDE runs under XP Pro and SQL SErver 2000 under Windows 2000?
Thanks
Dom<domtam@.hotmail.com> wrote in message
news:1130287729.767464.93730@.g43g2000cwa.googlegroups.com...
> Hi there. I understand that MSDE has limitation such as 25 concurrent
> users. However, under the same number of concurrent user environment
> (say, 1 user) and hardware, is it possible that there will be much
> performance difference between MSDE and SQL Server 2000?
> What if MSDE runs under XP Pro and SQL SErver 2000 under Windows 2000?
>
MSDE has no limit on concurrent users. It has a workload governer which
slows down processing when there are more than 5 concurrent workloads (not
users or connections). In addition databases are limited to 2GB each.
Within the limitations of MSDE the performance should be similar.
The replacement for MSDE, SQL Server 2005 Express Edition has no governor or
user limitation, but is limited to using 1GB of ram, 1 processor and
databases are limited to 4GB each.
David