speed up selection in SQL Query

5 pts.
Tags:
SQL
SQL Query
I am using a database,which is mainly using for Data retrieving purpose.What all are the tricks to increase the speed of select query from that database?

Answer Wiki

Thanks. We'll let you know when a new response is added.

That could depend on the available resources on the server, and could involve some configuration parameters specific to the DBMS you are using, which you did not specify.

Leaving those details out, in general you can improve performance on queries adding appropriate indexes to some tables. Good candidates for indexes are tables that are frequently used in queries and have large amounts of data. In general, the best columns to index on are those that are included in the WHERE clause of critical SQL statements, and usually you get better results when you index on columns that have many different values in the tables.

Also, depending on the type or queries you issue, performance could be improved by creating views (or materialized views).

—————— [kccrosser]

If your queries are known in advance, and they reference multiple columns of a table, creating a multi-column index that uses the columns of the where clause can significantly improve performance.

Sometimes, it is useful to create indexes with the primary key column(s) added on the end, even if they are not used in the where clause, as this allows you to create a Unique index, which the query optimizer will generally consider as more useful than a non-unique index. Also, if the results of a query against that table is a join against another table using the primary key from this table, if the pk column(s) are part of the index, this can save multiple physical accesses to the table, as the pk data is returned from the index.

Example:
create table MyTable (
pkcol integer not null,
lastname varchar(255) not null,
firstname varchar(255) not null,
…lots of data columns…,
constraint pkMyTable primary key (pkcol));

a)
Create index ixlastname on MyTable(lastname);
create index ixfirstname on MyTable(firstname);
select pkcol from MyTable where lastname = ‘SMITH’ and firstname = ‘JOHN’

This will do an index range search on ixlastname and an index range search on ixfirstname, then join them on the internal record ids, then physically access the table rows to get the values of the “pkcol” column for the records that match.

Assuming there are 1000 Smith’s and 2000 John’s in a table of millions of records, this could require fetching 3000 different index records, finding perhaps 200 matching persons, and then doing 200 physical table accesses to retrieve the pkcol values.

b) Using the same table, instead use:
create unique index uixName on MyTable(lastname, firstname, pkcol);
select pkcol from MyTable where lastname = ‘SMITH’ and firstname = ‘JOHN’

This will do a single index range search on uixName (very efficient) and directly return the pkcol values right from the index – no table accesses are required at all.

Assuming the same distribution as in (a), this would only do 200 row retrievals from the index, a more than 10:1 savings in cost.

NOTE: This usually only works when the index contains the given values in the initial columns in the index and the “extra” column(s) are the last columns. Consider an index like:
Create unique index uixBadIndex on MyTable(pkcol, lastname, firstname);
This index is only useful if you commonly retrieve the name fields given the pkcol value – it will NOT be used if you do a name query and want to retrieve the pkcol value.

This trick can be used with other commonly referenced columns as well. Where possible, order the index such that the first column of the index contains the most widely varying data that is supplied in the where clause, the next column has the next most widely varying data, etc., with the “unknown” column(s) the last ones in the index list.

Avoid non-materialized views where possible – particularly views on views. Views that reference other views are inherently poor performers, as the query engines tend to lose the ability to use indexes once they need to traverse through two or more levels of view definitions.

If you need to use views, or if you need to do complex joins on multiple tables, consider using materialized views to create “real” tables that can be explicitly indexed – these can be very fast to query.

Lastly, if your table is used heavily for queries and isn’t updated a lot (this applies to probably 99%+ of all tables out there), then: index, index, index…

When in doubt, add an index. If you do not know a priori what columns will be used in searches, then put indexes on just about every column. And – you may want to make every column unique by adding the primary key column(s) on the end of the index.

In the above examples, if sometimes you have people using just the last or first name, definitely create individual indexes on each of those. If the results of those queries then use the primary key column to do additional joins, then consider creating:

create unique index uixLastNamePK on MyTable(LastName, pkcols);
create unique index uixFirstNamePK on MyTable(FirstName, pkcols);

Yes – it is possible to have too many indexes, but 99 times out of 100 (or more) the problem is not enough indexes. You may want to be stingy with indexes if you have a table where there is a relatively high rate of insert/update/delete transactions compared to queries, as each change to the table requires all the indexes to be updated. Otherwise, look at your performance numbers and add indexes as needed.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following