+ Post New Thread
Results 1 to 6 of 6
Coding Thread, Make SQL Cursor code more efficient in Coding and Web Development; Hi, Any thoughts or views on this code please, it's not that it's particularly slow or anything, just thought there ...
  1. #1

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,969
    Thank Post
    673
    Thanked 1,383 Times in 1,145 Posts
    Rep Power
    350

    Make SQL Cursor code more efficient

    Hi,
    Any thoughts or views on this code please, it's not that it's particularly slow or anything, just thought there might be a better way than the cursor...?
    Ignore the reference to Students and subsequent reference to Staff, the original db was called students and i've just dumped the staff tables in there too. It all works okay.
    I figure there might be a better way to assign the the variables with a combined query rather than separate selects maybe ... ?

    Code:
    -- Write all database images (jpg) to file. --------- --------- --------- --------- --------- --------- ---------  
    use Students
    DECLARE CURSOR_ProductIds CURSOR FOR (SELECT [RECORD_NR] FROM [Students].[dbo].[Staff_card_info]
      WHERE Staff_card_info.UpdateTime > GETDATE()-0.04)
     
    DECLARE @ProductId INT; 
     
    OPEN CURSOR_ProductIds 
     
    FETCH NEXT FROM CURSOR_ProductIds INTO @ProductId 
    WHILE (@@FETCH_STATUS <> -1) 
    BEGIN 
      DECLARE @ImageData varbinary(max); 
      SELECT @ImageData = (SELECT convert(varbinary(max), Photo, 1) FROM dbo.staff_card_info WHERE Record_Nr = @ProductId); 
     
      DECLARE @path nvarchar(1024); 
      SELECT @path = 'C:\photo'; 
     
      DECLARE @filename NVARCHAR(1024); 
      SELECT @filename = (SELECT Second_Name + Left(First_Name,1) AS Name FROM dbo.Staff_card_info WHERE RECORD_NR = @ProductId); 
     
      DECLARE @FullPathToOutputFile NVARCHAR(2048); 
      SELECT @FullPathToOutputFile = @path + '\' + @filename + '.jpg'; 
     
      DECLARE @ObjectToken INT 
      EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT; 
      EXEC sp_OASetProperty @ObjectToken, 'Type', 1; 
      EXEC sp_OAMethod @ObjectToken, 'Open'; 
      EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @ImageData; 
      EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @FullPathToOutputFile, 2; 
      EXEC sp_OAMethod @ObjectToken, 'Close'; 
      EXEC sp_OADestroy @ObjectToken; 
     
      PRINT @ProductId
      PRINT @filename	 
      PRINT ' '
      
      FETCH NEXT FROM CURSOR_ProductIds INTO @ProductId 
      
    END 
    
    
    CLOSE CURSOR_ProductIds 
    DEALLOCATE CURSOR_ProductIds

  2. #2

    localzuk's Avatar
    Join Date
    Dec 2006
    Location
    Minehead
    Posts
    17,684
    Thank Post
    516
    Thanked 2,453 Times in 1,899 Posts
    Blog Entries
    24
    Rep Power
    833
    Personal preference for me has always been to split data from processing. So, the SQL server just serves up the records from simple SELECT requests, the client program does the processing, and then if records need changing, INSERT, UPDATE, DELETE are called. Keeps the complexity down, and prevents bottlenecks most of the time.

    But that's personal preference as I say.

  3. Thanks to localzuk from:

    vikpaw (27th November 2012)

  4. #3

    Join Date
    Mar 2011
    Location
    Bournemouth
    Posts
    280
    Thank Post
    16
    Thanked 74 Times in 64 Posts
    Rep Power
    21
    Quote Originally Posted by localzuk View Post
    Personal preference for me has always been to split data from processing. So, the SQL server just serves up the records from simple SELECT requests, the client program does the processing, and then if records need changing, INSERT, UPDATE, DELETE are called. Keeps the complexity down, and prevents bottlenecks most of the time.

    But that's personal preference as I say.
    I agree with this, except that its personal preference, its not, it's good practice. The SQL service account shouldn't even have permissions to write those files to the disk in the first place.

    I'm more than happy to supply vbscript, powershell or .net app to replace this process, but if you absolutely have to do it all on the SQL server, I'll have a look at it and get back to you.
    Last edited by ChrisMiles; 27th November 2012 at 11:52 AM.

  5. #4

    Join Date
    Mar 2011
    Location
    Bournemouth
    Posts
    280
    Thank Post
    16
    Thanked 74 Times in 64 Posts
    Rep Power
    21
    If it must be done in SQL, this is how I'd do it. You'd need to switch to a temporary table rather than a table variable if the binary data was very large, but as they're jpgs I'd imagine it wont be too bad.

    Code:
    USE Students
    
    DECLARE  @studentData TABLE
    (
    	ImageID INT IDENTITY PRIMARY KEY,
    	ImagePath VARCHAR(MAX),
    	ImageData VARBINARY(MAX)
    )
    DECLARE @CurrentRow INT
    DECLARE @RowCount INT
    DECLARE @ImagePath VARCHAR(MAX)
    DECLARE @ImageData VARBINARY(MAX)
    DECLARE @ObjectToken INT 
    
    INSERT INTO  @studentData (ImagePath, ImageData)
    SELECT
    	'C:\photo\' + Second_Name + LEFT(First_Name, 1) + '.jpg',
    	CONVERT(VARBINARY(MAX), Photo, 1)
    FROM dbo.staff_card_info
    
    SELECT @CurrentRow = 1
    
    SELECT @RowCount = COUNT(*)
    FROM  @studentData
    
    WHILE (@CurrentRow < @RowCount)
    BEGIN
    
    	SELECT @ImagePath = ImagePath, @ImageData = ImageData
    	FROM  @studentData
    	WHERE (ImageID = @CurrentRow)
    
    	EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT; 
    	EXEC sp_OASetProperty @ObjectToken, 'Type', 1; 
    	EXEC sp_OAMethod @ObjectToken, 'Open'; 
    	EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @ImageData; 
    	EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @ImagePath, 2; 
    	EXEC sp_OAMethod @ObjectToken, 'Close'; 
    	EXEC sp_OADestroy @ObjectToken; 
    
    	PRINT  @ProductId
    	PRINT  @filename	 
    	PRINT ' '
    
    	SELECT @CurrentRow = @CurrentRow + 1
    	
    END
    Note: Don't have a database to test so you may have to check for bugs.
    Last edited by ChrisMiles; 27th November 2012 at 12:05 PM.

  6. Thanks to ChrisMiles from:

    vikpaw (27th November 2012)

  7. #5

    Join Date
    Mar 2011
    Location
    Bournemouth
    Posts
    280
    Thank Post
    16
    Thanked 74 Times in 64 Posts
    Rep Power
    21
    If you want to use a cursor, this is more efficient:

    Code:
    USE Students
    
    DECLARE ImagesCursor CURSOR FAST_FORWARD FOR
    	SELECT
    		'c:\photo\' + Second_Name + Left(First_Name,1) + '.jpg' as ImagePath,
    		CONVERT(VARBINARY(MAX), Photo, 1) as ImageData
    	FROM dbo.Staff_card_info
    	WHERE UpdateTime > GETDATE()-0.04
     
    DECLARE @ImagePath NVARCHAR(MAX); 
    DECLARE @ImageData VARBINARY(MAX); 
    
    OPEN ImagesCursor 
     
    FETCH NEXT FROM ImagesCursor INTO @ImagePath, @ImageData
    
    WHILE (@@FETCH_STATUS <> -1) 
    BEGIN 
    	DECLARE @ObjectToken INT 
    	EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT; 
    	EXEC sp_OASetProperty @ObjectToken, 'Type', 1; 
    	EXEC sp_OAMethod @ObjectToken, 'Open'; 
    	EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @ImageData; 
    	EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @ImagePath, 2; 
    	EXEC sp_OAMethod @ObjectToken, 'Close'; 
    	EXEC sp_OADestroy @ObjectToken; 
    
    	PRINT  @ProductId
    	PRINT  @filename	 
    	PRINT ' '
    
    	FETCH NEXT FROM ImagesCursor INTO @ImagePath, @ImageData
    END 
    
    CLOSE ImagesCursor 
    DEALLOCATE ImagesCursor
    Last edited by ChrisMiles; 27th November 2012 at 12:11 PM.

  8. Thanks to ChrisMiles from:

    vikpaw (27th November 2012)

  9. #6

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,969
    Thank Post
    673
    Thanked 1,383 Times in 1,145 Posts
    Rep Power
    350
    You're right, it's just i didn't have the time and so went straight in for the data. Initially, i had a sixth former come up with some nifty php pages to look at and analyse the data, but as the photo was in a proprietary format it wasn't happy loading that, or doing anything with it. I started looking for a way to export them directly and came across that script. It turns out with a tweak on the client side software it allows export to jpeg, and so works. One thing i did have to change was a setting in SQL for those OA_sp.... procedures to work, which are turned off by default.

    With that client tweak the php page could have worked, but it was causing a problem getting all the data out for a jpeg, and only showing the first few lines, and now the script kiddie's gone back to uni'!

    An app would be good, but as it's only something i do, on an internal server it's no biggie if it's a script. I'll take a look at the SQL at any rate if it's better practise. Like i said, it wasn't that it was slow, i just saw in the other thread about using the Execution Plan to see the cost and realised it wasn't that efficient.

    I'll be interested to see how the new script analyses for cost.

SHARE:
+ Post New Thread

Similar Threads

  1. Replies: 0
    Last Post: 6th November 2012, 09:07 AM
  2. Making the procurement forums more visible? (Split from another thread)
    By SimpleSi in forum Comments and Suggestions
    Replies: 7
    Last Post: 13th May 2010, 07:56 AM
  3. Make a case for more staff
    By tazz in forum How do you do....it?
    Replies: 15
    Last Post: 25th September 2008, 08:13 AM
  4. SQL Code
    By wesleyw in forum Coding
    Replies: 3
    Last Post: 17th April 2008, 03:08 PM
  5. Making RDP more secure
    By mattx in forum Windows
    Replies: 2
    Last Post: 7th February 2007, 01:52 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •