473,224 Members | 1,595 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,224 software developers and data experts.

how do we tune a large sql query

hi,
I have a problem asked by one of my senior person and finding the
answer .
What is the step by step procedure for tune a large sql query.
OR how do we tune a large SQL query with somany joins

Feb 6 '06 #1
6 4451
kv********@gmail.com wrote:
hi,
I have a problem asked by one of my senior person and finding the
answer .
What is the step by step procedure for tune a large sql query.
OR how do we tune a large SQL query with somany joins


Assuming "tune" means "make faster". One methodology: look at the
execution plan and see where the DB spends the time. Then go from there.
See also http://www.sql-server-performance.com/

Kind regards

robert
Feb 6 '06 #2
>> What is the step by step procedure for tuning a large sql query. <<

What is the Good Life and how can I lead it, Socrates? Ghod, you work
for the iignorant.

Steps? There are only guidelines:
1) Have a normalized design, so the data has integrity.
2) The queries are a few magnitude of orders easily with 5NF
3) Trust thr optimizer, Luke

Feb 7 '06 #3
trust the optimizer, NOT.

wow. you START by trusting the optimizer. when stuff is slow, you dig
into it.

Feb 7 '06 #4
My point is a that slow code is usually the result of bad DDL, whcih in
turn leads to complex DML. You do not start by adding indexes and
hints. You fix the leak instead of mopping the floor, over and over.
But you remark is interesting, A friend of mine who writes database
engines for a major vendor observed that SQL Server people do not trust
their optimizer like Ingres, Postgres, DB2 people do. That is a bad
thing to say about the product.

The classic one was a test done by Fabian Pascal in DATABASE
PROGRAMMING & DESIGN years ago. It should still be in the CMP archives.
He wrote the same query seven different ways and ran them on various
SQL products on the same desktop hardware.

Ingres knew they were logically identical and produces the fastest
results of any
of the products. Oracle produced different execution plans for each
query and the slowest running one had to be shut down after grinding
for hours instead of seconds. The other products were all over the
place. But by now, everyone who is stil in business has improved since
those days.

Feb 7 '06 #5
The result is that many ms sql programmers do not trust the optimizer.

Your conclusion is that the optimizer in MS SQL can't be trusted.

Another possible conclusion is that there are no other engines widely
used.

I think the mssql engine can be trusted 99.9 percent of the time. But
if you step up into the big leagues, then you should have someone on
your team that knows what they are doing.

I know for a FACT that Oracle is the same way. Someone with good
business knowledge of the data can often speed large databases up.

Feb 26 '06 #6
>> A friend of mine who writes database
engines for a major vendor observed that SQL Server people do not trust

their optimizer like Ingres, Postgres, DB2 people do. That is a bad
thing to say about the product.
<<

that really depends on your perspective. For many practitioners RDBMS
as the whole package is much much more than just the optimizer. In many
situations the _whole solution_ implemented with SQL Server (including
DTS, reports, Web services etc.) is quite cheap to develop and to
maintain.

What's the point of developing the best optimizer in the world if all
the other links of the chain are weak?

Feb 26 '06 #7

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

Similar topics

3
by: Jason | last post by:
Does anyone know? Thanks so much.
10
by: | last post by:
I am trying to improve the robustness and elegance of my parametized sql statements in ASP 3.0 as they get passed to the sql server SP. Could anyone tell me if there are weaknessess in the way I...
1
by: gilgantic | last post by:
HELP!!! I am trying to fine tune or rewrite my SELECT statement which has a combination of SUM and CASE statements. The values are accurate, but the query is slow. BUSINESS RULE =============...
8
by: Egyd Csaba | last post by:
Hi All, how can I improve the query performance in the following situation: I have a big (4.5+ million rows) table. One query takes approx. 9 sec to finish resulting ~10000 rows. But if I run...
57
by: Bing Wu | last post by:
Hi all, I am running a database containing large datasets: frames: 20 thousand rows, coordinates: 170 million row. The database has been implemented with: IBM DB2 v8.1
0
by: Christoph Haas | last post by:
Hi, list... I have written an application in Perl some time ago (I was young and needed the money) that parses multiple large text files containing nested data structures and allows the user to...
2
by: blvandeb | last post by:
If anyone is able to provide advice for tuning the below query in sql server, it is much appreciated. In addition any index suggestions are also appreciated as I have access to the tables. Thank...
7
by: =?Utf-8?B?TW9iaWxlTWFu?= | last post by:
Hello everyone: I am looking for everyone's thoughts on moving large amounts (actually, not very large, but large enough that I'm throwing exceptions using the default configurations). We're...
8
by: rshivaraman | last post by:
Hi : I have a TableA with around 10 columns with varchar and numeric datatypes It has 500 million records and its size is 999999999 KB. i believe it is kb i got this data after running...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
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, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

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.