Optimize the query performance?

Hi,
I want to improve performance of my query to ensure that my query does not
timeout.
Scenario:
Table : EmpCheckInCheckOut
Felds: RowNumber,TokenID,CheckinTime,CheckoutTi
me,Action,EmpName
Query : Select TokenID,CheckinTime,CheckoutTime,EmpName
,RowNumber from
EmpCheckInCheckOut EC
WHERE (Action = 'In' AND NOT EXISTS ( SELECT TokenID FROM
EmpCheckInCheckOut EC2 WHERE Action = 'Out' and TokenID = EC.TokenID and
CheckinTime = EC.CheckinTime ) ) OR Action ='Out'
Through this query I get all those employees who have checked-in but not
checked out yet and employee who have done checkin and checkout both.
Clustered Index: Action,TokenIDCheckinTime
Query is working fine. But when I bulk insert 400000 records into this
table, query timeout.
So how well I can create Index on this table so that I get query performance
as well as less bulk insert time.
Please give your suggestion to optimize this query or do I need to change
the existing index and create some other index or something else?
Thanks in Advance
PushkarPushkar
It is probable page spliting casued by insertion. Either remove all indexes
just before BULK INSERT and re-create after or re-build them after the
insertion.
"Pushkar" <pushkartiwari@.gmail.com> wrote in message
news:OOnxDdFOGHA.4052@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I want to improve performance of my query to ensure that my query does not
> timeout.
> Scenario:
> Table : EmpCheckInCheckOut
> Felds: RowNumber,TokenID,CheckinTime,CheckoutTi
me,Action,EmpName
> Query : Select TokenID,CheckinTime,CheckoutTime,EmpName
,RowNumber from
> EmpCheckInCheckOut EC
> WHERE (Action = 'In' AND NOT EXISTS ( SELECT TokenID FROM
> EmpCheckInCheckOut EC2 WHERE Action = 'Out' and TokenID = EC.TokenID and
> CheckinTime = EC.CheckinTime ) ) OR Action ='Out'
>
> Through this query I get all those employees who have checked-in but not
> checked out yet and employee who have done checkin and checkout both.
> Clustered Index: Action,TokenIDCheckinTime
> Query is working fine. But when I bulk insert 400000 records into this
> table, query timeout.
> So how well I can create Index on this table so that I get query
> performance as well as less bulk insert time.
> Please give your suggestion to optimize this query or do I need to change
> the existing index and create some other index or something else?
> Thanks in Advance
> Pushkar
>
>|||Action is a poor choice for the first column of the clustered index.
The ideal is that the first column of any index, but most especially
the clustered, be highly selective. Action appears to be the least
selective choice available.
Looking at this query I woud suggest clustering on either of these:
TokenID,CheckinTime,Action
TokenID,Action,CheckinTime
If you do not change the clustering, then you need a second,
non-clustered index on one or the other of those.
For the bulk insert problem, if your regular processing removes all
rows from the table and then loads them again the idea of dropping the
indexes before the load and putting them back after is a good one. An
alternative would be to have another table,
EmpCheckInCheckOut_Imported, with the same layout but NO indexes. Bulk
insert into that table, then use INSERT/SELECT into the production
table. I would use an ORDER BY on the SELECT that matcches the
clustered index key - might help, can't hurt.
Roy
On Thu, 23 Feb 2006 14:28:04 +0530, "Pushkar"
<pushkartiwari@.gmail.com> wrote:

>Hi,
>I want to improve performance of my query to ensure that my query does not
>timeout.
>Scenario:
>Table : EmpCheckInCheckOut
>Felds: RowNumber,TokenID,CheckinTime,CheckoutTi
me,Action,EmpName
>Query : Select TokenID,CheckinTime,CheckoutTime,EmpName
,RowNumber from
>EmpCheckInCheckOut EC
> WHERE (Action = 'In' AND NOT EXISTS ( SELECT TokenID FROM
>EmpCheckInCheckOut EC2 WHERE Action = 'Out' and TokenID = EC.TokenID and
>CheckinTime = EC.CheckinTime ) ) OR Action ='Out'
>
>Through this query I get all those employees who have checked-in but not
>checked out yet and employee who have done checkin and checkout both.
>Clustered Index: Action,TokenIDCheckinTime
>Query is working fine. But when I bulk insert 400000 records into this
>table, query timeout.
>So how well I can create Index on this table so that I get query performanc
e
>as well as less bulk insert time.
>Please give your suggestion to optimize this query or do I need to change
>the existing index and create some other index or something else?
>Thanks in Advance
>Pushkar
>|||Does action track anythign other than check in and check out? If not, then
you could remove action entirely and just use the checkinTime and
CheckoutTime. You would know whether the employee checked out or not by
checking to see if Checkouttime was null. This assumes that, once checked
in, an employee cannot check in again until they have first checked out.
Having seperate rows for check in and check out, and seperate columns for
the same, seems redundant and can probably be simplified by removing on or
the other.
"Pushkar" <pushkartiwari@.gmail.com> wrote in message
news:OOnxDdFOGHA.4052@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I want to improve performance of my query to ensure that my query does not
> timeout.
> Scenario:
> Table : EmpCheckInCheckOut
> Felds: RowNumber,TokenID,CheckinTime,CheckoutTi
me,Action,EmpName
> Query : Select TokenID,CheckinTime,CheckoutTime,EmpName
,RowNumber from
> EmpCheckInCheckOut EC
> WHERE (Action = 'In' AND NOT EXISTS ( SELECT TokenID FROM
> EmpCheckInCheckOut EC2 WHERE Action = 'Out' and TokenID = EC.TokenID and
> CheckinTime = EC.CheckinTime ) ) OR Action ='Out'
>
> Through this query I get all those employees who have checked-in but not
> checked out yet and employee who have done checkin and checkout both.
> Clustered Index: Action,TokenIDCheckinTime
> Query is working fine. But when I bulk insert 400000 records into this
> table, query timeout.
> So how well I can create Index on this table so that I get query
performance
> as well as less bulk insert time.
> Please give your suggestion to optimize this query or do I need to change
> the existing index and create some other index or something else?
> Thanks in Advance
> Pushkar
>
>