August 8, 2008 at 1:54 am
Hi All,
The following statement is called from the front end application.
SELECT MAX(DOC_RECEIVED_DATE) from DC_DETAILS
Total No of Records in that Table : 500,000
Table Structure is
Create Table DC_DETAILS
(
DC_KEY_NUM int,
DOC_RECEIVED_DATE Datetime
)
It is an online appication, so approximately 100 users are accessing this application.
It is taking more than 10 seconds to retrieve the result set. But they want to get the result within 5 seconds.
So i suggested to conevrt it into a SP.
like
Create Procedure p1
as
begin
SELECT MAX(DOC_RECEIVED_DATE) from DC_DETAILS
End
One more point is , some records will be inserted into that table on some interval. say for example 200 records per day.
So i would like to know whether my suggestion is correct or wrong.
It would be appreciable if anybody give some other alternate method or solution to overcome this issue.
karthik
August 8, 2008 at 3:17 am
Simple index on DOC_RECEIVED_DATE would allow you to get result from that query in no time.
_____________
Code for TallyGenerator
August 8, 2008 at 5:01 am
Sergiy,
You mean , we need to create an index on DOC_RECEIVED_DATE column.
right ?
karthik
August 8, 2008 at 6:35 am
Any Inputs ?
karthik
August 11, 2008 at 1:32 am
Any inputs ?
karthik
August 11, 2008 at 4:26 am
You wanna say it did not work?
_____________
Code for TallyGenerator
August 11, 2008 at 4:36 am
Hi Sergiy,
Thanks for your idea ! It is working !
But i am expecting some more ideas !
karthik
August 11, 2008 at 4:42 am
Why?
_____________
Code for TallyGenerator
August 11, 2008 at 5:05 am
why ?
Just i wanted to know different ideas for a single problem !
Because i suggested another way also.
"Instead of runNing min() and max() function, we can kept that information in a master table,so that we can run our query against master table"
Say for example,
select min(dt),max(dt) from emp
Assume Record count is 100,000,000.
Obviously it should take some time for execution.
if we have table called 'System' with Min,Max column, we can get the result within a second.
CREATE TABLE system
(
JobName varchar(50),
JobStDate datetime,
JobEndDate datetime,
UserID varchar(30)
)
So for every job, the table capture single row only.
So running our query against 100,000,000 will differ from running against single row.
-----------
Just i wanted to know is there any approach to do the same one.
Sergiy,
Thats why i asked.
karthik
August 11, 2008 at 5:13 am
How many rows, on you opinion, server needs to read to find MAX or MIN value from an index?
_____________
Code for TallyGenerator
August 11, 2008 at 5:15 am
100,000,000 rows.
karthik
August 11, 2008 at 5:22 am
Did you ever use an index in your life?
For example, an index on a book?
How many pages you need to read to find what is the MIN and MAX keywords in a book if that book has keywords indexed?
_____________
Code for TallyGenerator
August 11, 2008 at 5:50 am
Hey Sergiy,
I do agree with you.:)
INDEX Will help us to achieve our requirement. Honestly, I am not offending you. Thanks for your idea.
But Simply, i wanted to know some different approaches, it is my objective !
karthik
August 11, 2008 at 6:00 am
It's not about an offense.
I have not noticed one.
It's about your reasons.
Index gives you desired result using single page reading.
It's theoretically minimal possible amount of reading. Because SQL Server reads data by pages.
So, no other method could be more effective.
Do you hope to find anything what requires less maintenance than an index?
What are you trying to improve?
_____________
Code for TallyGenerator
August 11, 2008 at 6:06 am
Do you hope to find anything what requires less maintenance than an index?
Obviously, index requires less maintenace.
Simply, i wanted to know other approaches. Because each people will think differently. Thats why, i wanted to share their ideas also.
karthik
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply