This is the most rediculous situation.
I have a sql server 2005 running on my computer.
When I go to the clients site they have set up a Sql server 2005 over there.
The query works on my computer but not on the clients machine.
I had them install the latest Service packs and hotfixes but no luck.
You don't have to read the entire query at the button here is what makes it stop working.
Or at least slow down enough to time out.
Its there where clause.
Without it it work great, with it it fails.
What funny is that when I have it set to just one check it works
WHERE Tbl_MainAgents.AgentCommissionCode ='3'
when I check for multiple values it fails
WHERE Tbl_MainAgents.AgentCommissionCode ='3'
or Tbl_MainAgents.AgentCommissionCode ='4'
or Tbl_MainAgents.AgentCommissionCode ='5'
I have trying doing the check using HAVING and I've tried it in the join, I've tried several different was of check for a 3, 4, or 5 but no luck
I even took a backup of the database on the clients machine, restored i on my machine and then it works.
I'm trying to avoid asking the clients to reinstall thier sql server. I already blamed there server by making them install the hotfixes.
here is the query :
SELECTMin([Qry_DetailCommType3&4&5_A].transaction_id)AS FirstOftransaction_id,
[Qry_DetailCommType3&4&5_A].store_no,
[Qry_DetailCommType3&4&5_A].store_name,
[Qry_DetailCommType3&4&5_A].transaction_date,
[Qry_DetailCommType3&4&5_A].PayableSubAgent,
Tbl_SubAgents.SubAgentDesc,
[Qry_DetailCommType3&4&5_A].PayableAgent,
Tbl_MainAgents.AgentName,
Tbl_MainAgents.TypeAS AgentType,
Tbl_MainAgents.AgentCommissionCode,
Tbl_AgentCommCode.AgentCommDesc,
Sum([Qry_DetailCommType3&4&5_A].NoOfCoupon)AS NoOfCoupon,
Sum([Qry_DetailCommType3&4&5_A].Food)AS Food,
Sum([Qry_DetailCommType3&4&5_A].NonHiloHattie)AS NonHiloHattie,
Sum([Qry_DetailCommType3&4&5_A].PrivateLabel)AS PrivateLabel,
Sum([Qry_DetailCommType3&4&5_A].Media)AS Media,
Sum([Qry_DetailCommType3&4&5_A].Others)AS Others,
Sum([Qry_DetailCommType3&4&5_A].SaleItems)AS SaleItems,
Sum([Qry_DetailCommType3&4&5_A].NonCommissionable)AS NonCommissionable,
Sum([Qry_DetailCommType3&4&5_A].InvalidClass)AS InvalidClass,
Tbl_SubAgents.C_PrivateLabel,
Tbl_SubAgents.C_Others,
Tbl_SubAgents.C_NonHiloHattie,
Tbl_SubAgents.C_Media,
Tbl_SubAgents.C_Food,
Tbl_SubAgents.C_SaleItems,
Sum([Qry_DetailCommType3&4&5_A].GwpMugs)AS GwpMugs,
Sum([Qry_DetailCommType3&4&5_A].GwpTshirt)AS GwpTshirt,
Sum([Qry_DetailCommType3&4&5_A].GwpCandies)AS GwpCandies,
Sum([Qry_DetailCommType3&4&5_A].GwpOthers)AS GwpOthers,
Sum([Qry_DetailCommType3&4&5_A].GwpSpecialty)AS GwpSpecialty,
Sum([Qry_DetailCommType3&4&5_A].GwpTowels)AS GwpTowels,
Tbl_SubAgents.MugsDeduction,
Tbl_SubAgents.CandiesDeduction,
Tbl_SubAgents.TShirtsDeduction,
Tbl_SubAgents.OthersDeduction,
Tbl_SubAgents.TowelsDeduction,
Tbl_SubAgents.SpecialtyDeductions,
Sum([Qry_DetailCommType3&4&5_A].CountOfgc)AS CountOfgc,
Sum([Qry_DetailCommType3&4&5_A].gross_line_amount)AS gc_amount,
Sum([Qry_DetailCommType3&4&5_A].[GCDeduc$])AS [GCDeduc$],
'Coupons'AS CommType,
[Qry_DetailCommType3&4&5_A].period,
[Qry_DetailCommType3&4&5_A].Month_Year
FROM(Tbl_MainAgents InnerJOIN Tbl_AgentCommCode ON Tbl_AgentCommCode.AgentCommCode = Tbl_MainAgents.AgentCommissionCode )
InnerJOIN([Qry_DetailCommType3&4&5_A] INNERJOIN Tbl_SubAgents ON [Qry_DetailCommType3&4&5_A].PayableSubAgent = Tbl_SubAgents.SubAgent)ON Tbl_MainAgents.MainAgent = Tbl_SubAgents.MainAgent
WHERE Tbl_MainAgents.AgentCommissionCode ='3'or Tbl_MainAgents.AgentCommissionCode ='4'or Tbl_MainAgents.AgentCommissionCode ='5'
GROUPBY [Qry_DetailCommType3&4&5_A].store_no,
[Qry_DetailCommType3&4&5_A].store_name,
[Qry_DetailCommType3&4&5_A].transaction_date,
[Qry_DetailCommType3&4&5_A].PayableSubAgent,
Tbl_SubAgents.SubAgentDesc,
[Qry_DetailCommType3&4&5_A].PayableAgent,
Tbl_MainAgents.AgentName,
Tbl_MainAgents.Type,
Tbl_MainAgents.AgentCommissionCode,
Tbl_AgentCommCode.AgentCommDesc,
Tbl_SubAgents.C_PrivateLabel,
Tbl_SubAgents.C_Others,
Tbl_SubAgents.C_NonHiloHattie,
Tbl_SubAgents.C_Media,
Tbl_SubAgents.C_Food,
Tbl_SubAgents.C_SaleItems,
Tbl_SubAgents.MugsDeduction,
Tbl_SubAgents.CandiesDeduction,
Tbl_SubAgents.TShirtsDeduction,
Tbl_SubAgents.OthersDeduction,
Tbl_SubAgents.TowelsDeduction,
Tbl_SubAgents.SpecialtyDeductions,
[Qry_DetailCommType3&4&5_A].period, [Qry_DetailCommType3&4&5_A].Month_Year
What's the error? Can you post it?
Adamus
|||I'm not at the client site now but I think eventualy a timeout message came.
Sometime I get a timeout, sometimes it just keeps going. I let the timer go for more than 16 minuets.
|||when I don't have the "where " in the query it only takes 9 seconds|||How many records are we talking about?
Is this a production server?
Is the table being locked somehow?
Is the field indexed?
If you add one check to the where clause does it take 9 seconds, then 2 checks = 18 secs?...
Just a few raw thoughts.
Adamus
|||Is there an index on Tbl_MainAgents.AgentCommissionCode ?
What does the query plan show?
Are their statistics current?
Have you run it through profiler?
|||It returns 20,000 records.
The server than can not handle this sql is in production.
On my my personal computer (which is not 1/2 as good as the production server) it works fine. Even with the where clause.
I don't know if the table is being locked. I assume not because other querries work.
And when I don't check for agentcommcode more than once it works.
Agentcommcode is a varchar(2). It is a primary key. I don't know why they made it a varchar(2) instead of an int. but I don't want to change it, might break some code somewhere.
with One check
where agencCommCode = '3' it takes 9 seconds
with 2 checks
where agencCommCode = '3' or agencCommCode = '4' take forever
I am very new to sql server 2005, I havn't tried statistics, or lookin at the query plan.
But I took a back up of the database from the production server.
Restored in on my own little PC. Won't all the indexes and query plan come over with that.
When I run the exact database that I backed up from production to my pc it works great.
I'd be interested in see the query plans generated for the differing WHERE clauses.
They haven't tried to do any manual "tuning" on the server have they? Like using fibers or restricting CPU selection, etc?
What else runs on this same machine?
Is there another DB used by another app that is intensive?
|||I can't answer some of these questions becuase I am not on the clients machine.
I am on my own computer, where everything works fine.
I guess I'll just tell them to reinstall thier sql server.
After that I'll go over there and if the problem still exists, I'll try looking at statistic, and query plan and all that.
If that doesn't work I might tell them to use sql server 2000. I've never had any problem with that. And I do miss enterprise manager.
|||Just out of mere curiousity, if you change the = '1' to LIKE '%1%' Does it effect the query time even with 3 checks?
(I know it'll return undesired results but I'm curious about the datatype)
Adamus
|||Again I am currently on my own computer, where everything works fine
changing to like didn't make any difference,
I'll try it on the clients machine and let you know. That is if we still have the problem after reinstalling sql serve 2005,
|||ksy
Do they have an anti-virus running on that production box?
If so, have they excluded file extensions .mdf, .ndf, and .ldf from the AV processing?
If AV is on the box and they haven't excluded the SS files, that can be an issue.
|||No there was not anitvirus on that machine.
I delete the table Tbl_AgentCommCode and its relationshipt with the table Tbl_mainAgent and then recreate this table.
This fixed it. I have no idea why. Whats funny is that before I recreate this table I tried my query without this table and it still didn't work.
Anyway now it works. Thanks everybody who tried to help.
|||Ok, but you don't want to do that every time a query is slow, do you?
It was likely one of a few things.
* Index Stats
* Index fragmentation
* Poor stored query plan (though if you altered it for the test that was probably not it.)
Updating stats would have probably done it, or rebuilding indexes. You really need to look at the plan of a query using Management Studio or Query Analyzer to see what isn't happening on one server and not the other. It will save you time in the future to try these things first.
sql