October 17, 2013 at 10:36 am
I have a database with a bunch (cca. 20,000) of associated images. I do not store the file path in the database, but use a stored procedure to find all image files associated with a particular record, whenever the record is loaded into an edit buffer.
This procedure is rather slow, several seconds sometimes. I use this command:
exec master.dbo.xp_DirTree @LocalRootPrilohy,1,1
with the directory path in the passed variable, "D:\Databaze\Paleontologie\Prilohy\Soukrome\L\".
The elapsed time seems about right for a physical scan of the drive, but it seems to me that the controller should have this info in cache and return it rather more quickly. Is there some setting that I might be missing that directs DirTree to use cached info or anything like that?
October 17, 2013 at 10:59 am
Use master..xp_cmdshell or CLR stored procedures instead.
master.dbo.xp_dirtree is an undocumented and unsupported procedure.
Jonathan Bernardez Bernardez
___________________________________________________________
DBD. MCSA SQL Server 2012
October 17, 2013 at 12:55 pm
jonysuise (10/17/2013)
Use master..xp_cmdshell or CLR stored procedures instead.master.dbo.xp_dirtree is an undocumented and unsupported procedure.
I was trying to avoid xp_cmdshell for all the security problems. Also, dirtree provides the file listing in a format I found quite useful. Do you know how to call a CLR from a stored procedure, and which one I should use, to get output as close to dirtree's as possible?
October 17, 2013 at 1:21 pm
Check this post. Might help.
http://carlosbercero.com/post/?post=Writing_a_CLR_Stored_Procedure_on_SQL_Server
Jonathan Bernardez Bernardez
___________________________________________________________
DBD. MCSA SQL Server 2012
October 17, 2013 at 2:29 pm
jonysuise (10/17/2013)
Check this post. Might help.http://carlosbercero.com/post/?post=Writing_a_CLR_Stored_Procedure_on_SQL_Server
Okay, thanks, I'll try that. I use Access, not VS, but the principles should be similar.
October 17, 2013 at 11:20 pm
jonysuise (10/17/2013)
Check this post. Might help.http://carlosbercero.com/post/?post=Writing_a_CLR_Stored_Procedure_on_SQL_Server
But back to the original question, actually. Do you know if any of these methods are faster? That was my interest in this, to try speeding up the response time.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply