Server.ScriptTimeout not timing out 1

[ksbigfoot]

In my ASP page, I have Server.ScriptTimeout = 100, I have a query that I run calling SQL Server 7.0 and it never seems to timeout.
Has anyone ran into this problem before?

Thanks

 

[DotNetGnat]

how much is the "Idle time of worker process" is set??

-DNG

 

[ksbigfoot]

Howdy DotNetGnat,
I am not sure where to find this.
Could you point me in the right direction.

 

[DotNetGnat]

try this link:

http://www.microsoft.com/technet/pr...IIS/83b35271-c93c-49f4-b923-7fdca6fae1cf.mspx

-DNG

 

[ksbigfoot]

Sorry DotNetGnat,
I should of mentioned this is running on Windows 2000 Server. So I think that IIS 5.0 runs under this.

 

[DotNetGnat]

ok..lets step back a bit and come to the main problem...so when you run this query...do you get any errors...what actually happens and what is this query doing..may be there is an alternate efficient way for this query...

-DNG

 

[ksbigfoot]

I ran my query through the Query Analyzer. I am using a UNION query. If I run my first query, it pulls back ZERO records in under a second. I run the second part of my query and I pull back 694 rows in under a second. But when I run both together with a UNION, it is hanging.
I realize at this point that I maybe should move to the SQL Server forum, what do you think?

 

[DotNetGnat]

if you want..i can take a look at the query before you can post it in sql forums...of course i will look there too...because i participate in that forums too...

just post some sample data and results you want to see...also post the queries you tried...we will see if we can come up with the solution...

or else you can go ahead and post it in sql server forums...you are sure to get the reply there...lot of experts there

-DNG

 

[ksbigfoot]

Howdy DotNetGnat,
Here is the query:
SELECT Company.CompanyID, Offices.OfficeID, Offices.ChangeID, Offices.CreatedBy, Offices.OfficeName, CompanyType.CompanyTypeDesc, Company.CompanyName, IndustryType.IndustryTypeDesc, Offices.City, ServiceType.ServiceTypeDesc FROM IndustryType INNER JOIN (ServiceType INNER JOIN (CompanyType INNER JOIN (CompanySpecs INNER JOIN (Company LEFT JOIN Offices ON Company.CompanyID = Offices.CompanyID) ON CompanySpecs.CompanySpecsID = Company.CompanySpecsID) ON CompanyType.CompanyTypeID = CompanySpecs.CompanyTypeID) ON ServiceType.ServiceTypeID = CompanySpecs.ServiceTypeID) ON IndustryType.IndustryTypeID = CompanySpecs.IndustryTypeID WHERE Offices.CreatedBy = 6623 AND CompanySpecs.IndustryTypeID = 1 AND CompanySpecs.CompanyTypeID = 2 AND Offices.ChangeID IS NOT NULL AND Offices.ChangeID NOT IN (SELECT OfficeID FROM SupportServiceAssignment WHERE CreatedBy = 6623 AND PropertyID = 3607)
UNION
SELECT Company.CompanyID, Offices.OfficeID, Offices.ChangeID, Offices.CreatedBy, Offices.OfficeName, CompanyType.CompanyTypeDesc, Company.CompanyName, IndustryType.IndustryTypeDesc, Offices.City, ServiceType.ServiceTypeDesc FROM IndustryType INNER JOIN (ServiceType INNER JOIN (CompanyType INNER JOIN (CompanySpecs INNER JOIN (Company LEFT JOIN Offices ON Company.CompanyID = Offices.CompanyID) ON CompanySpecs.CompanySpecsID = Company.CompanySpecsID) ON CompanyType.CompanyTypeID = CompanySpecs.CompanyTypeID) ON ServiceType.ServiceTypeID = CompanySpecs.ServiceTypeID) ON IndustryType.IndustryTypeID = CompanySpecs.IndustryTypeID WHERE Offices.ChangeID IS NULL AND CompanySpecs.IndustryTypeID = 1 AND CompanySpecs.CompanyTypeID = 2 AND (Offices.CompanyID = Offices.CreatedBy OR Offices.CreatedBy = 6623OR Offices.CreatedBy = 6624) AND Offices.OfficeID NOT IN (SELECT OfficeID FROM SupportServiceAssignment WHERE CreatedBy = 6623 AND PropertyID = 3607) AND Offices.OfficeID NOT IN (SELECT ChangeID FROM Offices WHERE ChangeID IS NOT NULL AND CreatedBy = 6623)
ORDER BY Company.CompanyName, Offices.OfficeName


Sample data:
CompanyID = 1234
OfficeID = 53
ChangeID = NULL
CreatedBy = 5559
OfficeName = 'Head Office'
CompanyTypeDesc = 'Contractor'
CompanyName = 'ksbigfoot resources'
IndustryTypeDesc = 'Petroleum'
City = 'Knotsville'
ServiceTypeDesc = 'General'

Thanks again

 

[DotNetGnat]

in your second query..i noticed this:

Offices.CreatedBy = [red]6623OR Offices.CreatedBy[/red] = 6624)

no space between 6623 and "OR"

i am still looking...

-DNG

 

[DotNetGnat]

oops..i meant [red]6623OR Offices.CreatedBy[/red]

also remove order by clause from the second query...

-DNG

 

[ksbigfoot]

Wow, I looked at the query numerous times and I didn't see the space missing. Thanks so much for that.
I ran the query without the order by clause and fixed the space and it runs in 1 second.

How come I can't use an order by clause?
How else can I order the records that are returned?

 

[Sheco]

You might be able to sort it using the recordset object... or put this query into a stored procedure that returns a sorted resultset.

 

[ksbigfoot]

Hi Sheco,
I will try putting the query into a recordset.

DotNetGnat, I gave you a star for finding the problem.
Sheco, If the stored procedure works, I will come back and give you a star also.

Thanks
ksbigfoot

 

[DotNetGnat]

glad to be of help...also here is the thing...

The ORDER BY clause applies to the entire result. You can only have one ORDER BY clause in a UNION query and it must be part of the last SELECT statement.

also change UNION to UNION ALL

so what i would suggest it change your query to look something like this...example:

SELECT au_lname FROM authors
UNION ALL
SELECT lname FROM Employee
ORDER BY au_lname

-DNG