Re: Database query performance
- From: "William \(Bill\) Vaughn" <billvaNoSpam@xxxxxxxxx>
- Date: Wed, 22 Jun 2005 18:29:35 -0700
I'm often frustrated with developers who complain about Access/JET
performance. It's like returning a bicycle to the store because it could not
deliver a ton of coal from the basket on its handlebars.
Just a thought. All too often returning too many rows (far more than are
needed), moving entire tables into memory, and other "bulk" operations are
the cause of the issues. Is your design suffering from a single-use
application or many users attempting to use the data. I've found JET to be
adequate (and pretty fast) when used within its limits and wisely.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"KDV" <kdv@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3BFFBF73-6CC0-4DD3-8692-08B9BDD81AE9@xxxxxxxxxxxxxxxx
> Just to clarify that the database is Access.
>
> I still could not figure out how to improve the performance. If I cannot
> use
> sub queries then what are the alternatives. I have to make use all of the
> columns so I have to use Select *. Indexing of DateTime column does not
> help
> much. Can anybody explain with an example.
>
> Thanks
> KDV
>
>
> "William (Bill) Vaughn" wrote:
>
>> I would also change the column name from DateTime (a reserved word) to
>> Date_Time or some other spelling.
>> To see how the server is handling the query, you can turn on the query
>> plan
>> and see if the engine is doing a rowscan or an index hit. I would also
>> avoid
>> use of SELECT *. It returns columns that may or may not be needed by your
>> application.
>>
>> --
>> ____________________________________
>> William (Bill) Vaughn
>> Author, Mentor, Consultant
>> Microsoft MVP
>> www.betav.com/blog/billva
>> www.betav.com
>> www.sqlreportingservices.net
>> Please reply only to the newsgroup so that others can benefit.
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> __________________________________
>>
>>
>> "WJ" <JohnWebbs@xxxxxxxxxxx> wrote in message
>> news:OIMIru1dFHA.2180@xxxxxxxxxxxxxxxxxxxxxxx
>> >
>> > "KDV" <kdv@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> > news:BCABD84C-69DF-45B0-B6DB-75FF9065A451@xxxxxxxxxxxxxxxx
>> >>
>> >> SELECT * from CompanyDetail AS X
>> >> WHERE [DateTime]=(SELECT max([DateTime]) FROM CompanyDetail WHERE
>> >> Company=X.Company)
>> >> ORDER BY Company
>> >>
>> >
>> > 1. I would index the field [DateTime]. Very fast if it is indexed.
>> >
>> > 2. I would revise the above SQL script to
>> >
>> > "select top 1 * from CompanyDetail order by [DateTime] desc"
>> >
>> > this query script (run on MS/SQL Server) would return 1 record in a
>> > heart-beat since you are only interested in the lattest one ?
>> >
>> > John
>> >
>> >
>>
>>
>>
.
- Follow-Ups:
- Re: Database query performance
- From: KDV
- Re: Database query performance
- References:
- Database query performance
- From: KDV
- Re: Database query performance
- From: WJ
- Re: Database query performance
- From: William \(Bill\) Vaughn
- Re: Database query performance
- From: KDV
- Database query performance
- Prev by Date: Re: Datasets and identities in ADO.NET 2.0
- Next by Date: DataReader error
- Previous by thread: Re: Database query performance
- Next by thread: Re: Database query performance
- Index(es):
Relevant Pages
|
