Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
chrispy
Posting Yak Master
107 Posts |
 Posted - 2002-03-19 : 15:05:24
|
| All, In the process of converting some tables from Access to MS SQL I am also trying to optimize some queries. I have one that honestly I do not know where to start at and need a point in the right direction. If one of the experts here might not try to point me in the right direction it would be greatly appreciated. I am copying the queries from Cold Fusion but I it should be simple an clear as to what is happening. More or less the main Query GetCompnay is being looped over by the <cfloop> that you see below. The #variables# that are in the queries that are under the cfloop are from the main Query "GetCompany" . As this loop is very time consuming I am lookin the convert it all to TSQL and than an SP. I am terrrible at doing queries on queries and I think that I just need a point in the correct direction. Not someone to do the whole thing. As yuou can hopefully see we are only trying to get the recordcounts for each Company. These are the only reason for the queries inside the loop.Thanks in advance,ChrisHere is the code:<CFQUERY NAME="GetCompany" DATASOURCE="#db#">Select Company, AcctNM, Comp_Posting_Max, Comp_Resume_MaxFROM AccountINIWHERE AcctNM='#Account#'ORDER BY Company</CFQUERY><CFloop QUERY="GetCompany"> <CFQUERY NAME="GetPostJobCount" DATASOURCE="#db#"> select post FROM Job WHERE AcctNM = '#AcctNM#' AND Post=1 AND JobDelete=0 </CFQUERY> <cfset PostedJobCount = #GetPostJobCount.RecordCount#> <CFQUERY NAME="GetJobCount" DATASOURCE="#db#"> select post FROM Job WHERE AcctNM = '#AcctNM#' AND JobDelete=0 </CFQUERY> <cfset JobCount = #GetJobCount.RecordCount#> <CFQUERY NAME="GetResumeCount" DATASOURCE="#db#"> select ID FROM Resumes WHERE AcctNM = '#AcctNM#' </CFQUERY> <cfset ResumeCount = #GetResumeCount.RecordCount#> <CFQUERY NAME="GetSiteVisit" Datasource="#statdb#"> Select ThisDate FROM ORStatLog Where AcctNM = '#AcctNM#' AND Type='2' AND ThisDate > #Today# </cfquery> <cfset SiteVisitCount = #GetSiteVisit.RecordCount#> <CFQUERY NAME="GetJobVisit" Datasource="#statdb#"> Select ThisDate FROM ORStatLog Where AcctNM = '#AcctNM#' AND Type='1' AND ThisDate > #Today# </cfquery> <cfset JobVisitCount = #GetJobVisit.RecordCount#></cfloop> |
|
|
chrispy
Posting Yak Master
107 Posts |
Posted - 2002-03-19 : 17:46:54
|
| Jay, Thanks a million that worked like a charm first time. I fell spoiled. :)Chris |
 |
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2002-03-20 : 05:18:39
|
| 47? |
|
|
|
davidpardoe
Constraint Violating Yak Guru
324 Posts |
Posted - 2002-03-20 : 05:50:13
|
quote:
47?
It must be Jay's secret piece of code for ensuring that he knows who copies his code. If you find a count(47) it must have been written by Jay  ============The Dabbler! |
|
|
|
chrispy
Posting Yak Master
107 Posts |
Posted - 2002-03-20 : 09:13:29
|
| I was wondering that as well??? Sorry Jay but I did use *Neitherless with this DB going from access to SQL and recreating this query we went from 380 seconds to 1.4 seconds. Thanks again,Chris |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-03-20 : 10:04:41
|
| the way Jay is telling stories , i thought thts his age-------------------------------------------------------------- |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-03-20 : 13:11:44
|
quote:
<O>
This is a curious signature line that Jay is using... I saw that setbasedisthetruepath uses it too...------------------------GENERAL-ly speaking... |
|
|
|
|
|
|
Need point in the right direction!