473,237 Members | 1,278 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,237 software developers and data experts.

sql 2005 - optimization - cannot use index

I have a very very strange situation with a particular application and sql
server 2005 enterprise.

This application combines numerical data from multiple tables. User can make
query over this kind of tables, can build queries with "group by" "order by"
and "join", "sum, count(*) ecc. on many many columns.

I cannot know the query that application is going to build, so I do not know
how to create indexes.
I cant make indexes on all the columns of course, so I'm creating some index
over columns that should be statistically used in the join, but when there
is a group by on a column chosen from the user, I realize that the plan
become non efficient with the famigerate "TABLE SCAN".

Can somebody, give me an idea, to optimize this situation.

Thanx

Massimo
Jan 26 '07 #1
4 2806
Massimo (ma*****@hotmail.it) writes:
I have a very very strange situation with a particular application and sql
server 2005 enterprise.

This application combines numerical data from multiple tables. User can
make query over this kind of tables, can build queries with "group by"
"order by" and "join", "sum, count(*) ecc. on many many columns.

I cannot know the query that application is going to build, so I do not
know how to create indexes.
I cant make indexes on all the columns of course, so I'm creating some
index over columns that should be statistically used in the join, but
when there is a group by on a column chosen from the user, I realize
that the plan become non efficient with the famigerate "TABLE SCAN".

Can somebody, give me an idea, to optimize this situation.
My gut reaction is that I would want more information about how
the users will use the system. Will all combinations be equally common?
Are some important than others? Are some combinations so completely
meaningless, that the users should even be prevented from trying them
(as doing that could cause performance issues).

Maybe you should pre-aggregate data. But that leads to the thought that
this is something which should be in Analysis Services instead. (I have
no experience of Analysis Services, so I cannot say much more.)

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jan 26 '07 #2
Thanx for your answer, it's a very particular application, it wants to
explode some result from money activities.
It is used by banks. I cannot say more.
I'm an oracle dba (ocp), and I would use Partitioning as I do sometimes in
oracle, on the column in join in this case, to try to increase at least some
kind of parallelism, but the Database Tuning Advisor, with a trace of a day
of activity never suggests me, this kind of approach. So I think it is not a
good way.

So the only way, I'm thinking to follow is to speed up "table scan", but how
?

Oracle with the same database, the same data and the same indexes, is more
flexible and often succeed in using indexing, without any kind of HINTS in
the queries.

The sql 2005 engine doesnt understand, the way to use an index even if it
has a subset of the columns he needs, as oracle does.

With a particular query the DTA suggests to index all the columns in the
WHERE + all the columns in the GROUP BY + all the columns in the ORDER BY +
use the INCLUDE clause with the columns in the SELECT statement with
functions like SUM, ecc.

Massimo


"Erland Sommarskog" <es****@sommarskog.seha scritto nel messaggio
news:Xn**********************@127.0.0.1...
Massimo (ma*****@hotmail.it) writes:
I have a very very strange situation with a particular application and
sql
server 2005 enterprise.

This application combines numerical data from multiple tables. User can
make query over this kind of tables, can build queries with "group by"
"order by" and "join", "sum, count(*) ecc. on many many columns.

I cannot know the query that application is going to build, so I do not
know how to create indexes.
I cant make indexes on all the columns of course, so I'm creating some
index over columns that should be statistically used in the join, but
when there is a group by on a column chosen from the user, I realize
that the plan become non efficient with the famigerate "TABLE SCAN".

Can somebody, give me an idea, to optimize this situation.

My gut reaction is that I would want more information about how
the users will use the system. Will all combinations be equally common?
Are some important than others? Are some combinations so completely
meaningless, that the users should even be prevented from trying them
(as doing that could cause performance issues).

Maybe you should pre-aggregate data. But that leads to the thought that
this is something which should be in Analysis Services instead. (I have
no experience of Analysis Services, so I cannot say much more.)

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Jan 26 '07 #3
Massimo (ma*****@hotmail.it) writes:
Thanx for your answer, it's a very particular application, it wants to
explode some result from money activities.
Data mining? In such case, Analysis Services is definitely your guy.
Oracle with the same database, the same data and the same indexes, is more
flexible and often succeed in using indexing, without any kind of HINTS in
the queries.

The sql 2005 engine doesnt understand, the way to use an index even if it
has a subset of the columns he needs, as oracle does.
The bitter truth is that if you try to implement an Oracle solution on
SQL Server, it is not going to turn out well. Just like an SQL Server
solution on Oracle would be bad.
With a particular query the DTA suggests to index all the columns in the
WHERE + all the columns in the GROUP BY + all the columns in the ORDER
BY + use the INCLUDE clause with the columns in the SELECT statement
with functions like SUM, ecc.
Yes, that gives you a covering index. This means that when the optimizer
has to scans the data, it only has to scan the relevant columns. This
means fewer pages to read, which means better performance. But if the
index is constrained to the WHERE clause only, locating the rows will
be faster. However, if the WHERE clause qualifies many rows, the
bookmark lookup for each row will be so expensive that a table scan
is better.

Unfortunately, with the tiny inforamtion I have about your system, it's
difficult to give very detailed advice.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jan 27 '07 #4
Thank you very much Erland,
it's difficult to give more informations, and I cannot detail it more.

You are right about oracle and sql server porting;
application should be tuned and live with the same RDBMS.

So definitively I'm suggesting to optimize what is possible:

-use best index (statistically)
-defrag indexes
-use dta on a typical workload (workload written on a trace with profiler
with "tuning" template for some days)
-evaluate partitioning on the column in join on the most accessed table

Bye
Massimo


"Erland Sommarskog" <es****@sommarskog.seha scritto nel messaggio
news:Xn**********************@127.0.0.1...
Massimo (ma*****@hotmail.it) writes:
Thanx for your answer, it's a very particular application, it wants to
explode some result from money activities.

Data mining? In such case, Analysis Services is definitely your guy.
Oracle with the same database, the same data and the same indexes, is
more
flexible and often succeed in using indexing, without any kind of HINTS
in
the queries.

The sql 2005 engine doesnt understand, the way to use an index even if
it
has a subset of the columns he needs, as oracle does.

The bitter truth is that if you try to implement an Oracle solution on
SQL Server, it is not going to turn out well. Just like an SQL Server
solution on Oracle would be bad.
With a particular query the DTA suggests to index all the columns in the
WHERE + all the columns in the GROUP BY + all the columns in the ORDER
BY + use the INCLUDE clause with the columns in the SELECT statement
with functions like SUM, ecc.

Yes, that gives you a covering index. This means that when the optimizer
has to scans the data, it only has to scan the relevant columns. This
means fewer pages to read, which means better performance. But if the
index is constrained to the WHERE clause only, locating the rows will
be faster. However, if the WHERE clause qualifies many rows, the
bookmark lookup for each row will be so expensive that a table scan
is better.

Unfortunately, with the tiny inforamtion I have about your system, it's
difficult to give very detailed advice.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Jan 27 '07 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Andrew Carlson | last post by:
system.BadImageFormatException: Field token out of range. Hello, I am trying to use the new Beta 2. I am not sure if this is even the place to ask. After installing, I was anxious to play with...
7
by: pankaj_wolfhunter | last post by:
Greetings, While loading data into Sql Server using BCP utility, we have to explicitly need to drop the indexes. then after loading the data we recreate them. I just want to know if this is...
3
by: R.A.M. | last post by:
Hello, Could you explain me please the following error: "Database 'DemoDotNET' cannot be opened due to inaccessible files or insufficient memory or disk space. (Microsoft SQL Server, Error:...
3
by: jamil | last post by:
I have developed an EXE that executes successfully when it exists and is launched from a Win2000 or XP client PC. If I was to copy this EXE to a network share, where the client has full admin...
5
by: cmrhema | last post by:
Hello Help please I am working on vb.net 2005 I wrote the coding as below I have developed a console application When i run the program i get an error stating smtpexception was unhandled...
0
by: Piero 'Giops' Giorgi | last post by:
Hi! I'm using the Thesaurus search on a SQL Server 2005 Db The thesaurus file is built like this (Reduced version) <XML ID="Microsoft Search Thesaurus"> <thesaurus...
4
by: Artie | last post by:
A few months ago a customer moved from SQL 2000 to SQL 2005. The db was backed up on SQL 2000 and restored to SQL 2005. The application using this data works on SQL 2005 but takes no advantage of...
9
by: dpatel75 | last post by:
I am trying to copy a database from a SQL 2000 SP3 Windows 2000 server to a 2005 SP2 Windows 2003 server. I am trying to use detach and attach method (have tried both within Management Studio and...
1
by: newphpcoder | last post by:
Good day! I am newly in SQL Server 2005 and now I encountered error when i try to login. This is some of the information: Microsoft SQL Server 2005 Express Edition Server Type: Database Engine...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.