how can i save time ?
-
where from i will get query plan !
then i'll have to make a table with how many records ? 1 lakhs ?
well, i will write a java program.
the program will use an infinite loop to pour random data into a DB table.
i think this table will now be the best example for a speed up query demonstration...right ?
-
by the way,
do you know any such book which exactly tell me the solution for this kind of problem ?
can you please tell me the name of book so that i can go through and learn this concept.
i know there are so many DB books ....but it would be easier for me if you kindly shortlist the book name for this purpose....i'll visit the library to get in touch with this.
-
I now understand that you don't have a specific question about a specific query.
Go in the search section of this site. Check the articles box and input "performance tuning".
Read on and post back if you have questions. We can't just start teaching you this from scratch. It takes too long to master (I'm not even there yet !!!).
-
I agree with Remi, it takes years of experience and even then you're only just scratching the surface.
If you're interested in a book then I can recommend 'inside SQL server' by Kalen Delany, it's well written and I don't think i know of a single DBA that hasnt got a copy.
-
Sorry I have not learned any of that stuff in books. Only online from this site and a couple articles on other sites.
-
Well you'll have to send me one cause I ain't got it yet.
Need my adress
? -
lol
-
i think you meant this book
Inside Microsoft SQL Server 2000
by Kalen Delany
ah...i can read this book online from 24x7 book online.
i see it has 2 chapters
Chapter 16 - Query Tuning
Chapter 17 - Configuration and Performance Monitoring
i'll have a look into it .
i'll back if i dont understand it.
i will appreciate if you list out couple of such books.
thanks for the information
-
Alright, good luck!
-
Hi,
here i have found one optimization trick...
In this section, we’ll look at a query that requests information about orders that have been placed by an employee whose employee ID is 4. The query is shown here:
SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM Orders
WHERE EmployeeID = 4
In this employee’s organization, each employee handles a small portion of the total orders, so you might expect SQL Server to use the EmployeeID index when processing the query. Instead, Query Analyzer informs you that the access method SQL Server will use is the PK_Orders clustered index, as shown in Figure 35-7.
To direct the query optimizer to use the EmployeeID index instead, you can use a hint with the SELECT statement, as shown in the following code. (Hints are discussed in the section “Using Hints” later in this chapter.)
SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM Orders WITH (INDEX(EmployeeID))
WHERE EmployeeID = 5
Note
In Microsoft SQL Server 7, the preferred index hint was index=index_name. With SQL Server 2000, the index hint index(index_name) is preferred.
By providing this extra information in the command, you instruct the query optimizer to use the execution plan that you want, rather than the one that the query optimizer has chosen for you. The adjusted Estimated Execution Plan pane is shown in Figure 35-8. As you can see from the data access method displayed, the EmployeeID index will be used as the input to a bookmark lookup, which will then retrieve the data from the database. (A bookmark lookup searches for an internal identifier for a row of data.)
The query optimizer is an efficient tool and constantly updates statistics to enable it to choose the best execution plan. But because you understand your organization and your data, in some cases, you might be better equipped than the query optimizer to choose the best execution plan.
Caution
When you use a hint to override the query optimizer, you do so at your own risk. Although there is little or no danger of data loss or corruption, you can adversely affect your system’s performance.
Its ok....but this is actually not speed up the query for a HUGE data set ....or is it ?
-
oh..yes...thats from book
Microsoft SQL Server 2000 Administrator's Companion
by Marci Frohock Garcia, Jamie Reding, Edward Whalen and Steve Adrien DeLuca
Microsoft Press © 2000
-
Most of us define huge as 100 millions rows. And we count how many million of rows/sec we can process. How do you define huge, and what the heck are you trying to do????????
-
hi,ok.
so is this all about query tuning ?
it seems easy then .
i need to check indexing on columns and have a stop-watch with me to see the time of result.
its a bit R&D kind of work i think.
but is it all about query tuning ?
-
Absolutely not, there's a whole load of things that you can look at for query turning.
indexing only takes you so far, a badly written query will always be slower than a well written one even with decent indexing
You really need to put in the work on this to get better at it, there's no quick fix i'm afraid
-
Let me put this in a perspective you can understand.
Read this (48 articles) :
http://www.sqlservercentral.com/articles/articleList.asp?categoryid=37
Then this (no clue how many but like 100) :
http://www.sql-server-performance.com/articles_performance.asp
Then those 10 will come in very handy :
http://www.sql-server-performance.com/articles_audit.asp
Once you do that, optimize your first 1000 queries. Then you'll start to have an idea about what performance tuning is all about!!
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply