question about a system query
-
We connect to sql server using odbc in one of our programs. we do the following query to get field information:
SELECT CAST(C.NAME AS VARCHAR(30)) FROM AUT103_DATA.DBO.SYSOBJECTS O,AUT103_DATA.DBO.SYSCOLUMNS C where o.xtype = 'U' and o.id=c.id and o.name = 'LEADS' and (c.status & 0X80) = 0X80
For some reason, this query takes 20 seconds to execute for one of our clients. We are doing other querys on the sysobjects table and they also seem to take a long time. Any ideas why this would be??
-
How long does it take to run...
Select Count(*) From (
SELECT CAST(C.NAME AS VARCHAR(30))
FROM AUT103_DATA.DBO.SYSOBJECTS O,AUT103_DATA.DBO.SYSCOLUMNS C
where o.xtype = 'U' and o.id=c.id and o.name = 'LEADS' and (c.status & 0X80) = 0X80
) AIf faster, then maybe its on the "transfer of the data" end of equation.
Once you understand the BITs, all the pieces come together
-
Sorry,
you need to change
SELECT CAST(C.NAME AS VARCHAR(30))
to
SELECT CAST(C.NAME AS VARCHAR(30)) as Colx
Once you understand the BITs, all the pieces come together -
OK, we narrowed it down to the GetFieldValues, on this query
SELECT CAST(C.NAME AS VARCHAR(30)),CAST(T.NAME AS VARCHAR(30)),T.TYPE
FROM TESTSQLDB.DBO.SYSOBJECTS O,TESTSQLDB.DBO.SYSTYPES T,
TESTSQLDB.DBO.SYSCOLUMNS C
WHERE o.name like 'TESTSQLVW'
AND user_name(o.uid) like '%dbo%' AND o.id = c.id
AND o.type <> 'P' AND c.xusertype = t.xusertype
For some reason, the first time we run through this query to retrieve fieldinfo, it takes almost a minute to get all of the information using getfieldvalue. (There were 85 iterations through the recordset, to get 85 field infos.)
Do you know of another way in which to get information abotu the fields using odbc??
Or maybe a reason/way to speed this up?
-
Have you tried?
sp_sproc_columns
* Noel
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply