May 20, 2004 at 8:26 am
Hello,
I have searched the forums but can't quite find the answer. I have an Access2K front-end and SQL2K backend that has tables with Word doc.s. All my queries are enormously slow when retrieving the Word doc.s.
The backend use to be Access but out grew the 2-gig barrier. What I use to query in 30 seconds with an access backend now takes over 15 minutes in SQL. It doesn't matter if it is an ODBC link or a converted ADP (project), the time is the same.
The Access backend was on the exact same server that the SQL is now on, so the hardware hasn't changed.
I have read many articles about pointing to image files in SQL and know that when I attach the Word doc. inside the front-end of Access it shows them as "Linked". Isn't that just a pointer? I have kept the images in a folder on the server for years and could easily edit the files in Word so I know that they are linked but don't know if the link is different than what is described as a pointer in SQL.
I'm new to SQL but feel like it should be faster even with the Access ODBC link. I know they are slow but this seems extreme.
Anyone have any ideas to try?
Bryan
May 21, 2004 at 7:09 am
Bryan,
I have a couple of databases in which I store Word documents that are stored and retrieved from within a Powerbuilder application.
Have you verified that you have the necessary indexes created on the table(s) containing the Word documents? Hopefully, you've defined a primary key on all of your tables, plus any unique indexes, and indexes on commonly queried columns.
Do you store a copy of the document in a SQL Server text or image column, or just the pathname in a char() or varchar() column?
Mike
May 21, 2004 at 9:27 am
Thanks for the reply Mike!
I have defined a Primary key in the Table that holds the Word Doc.s but nothing more with indexes has been done. The fields are definitely IMAGE but I am assuming they are linked because (inside the Access front-end) I am selecting “Linked object” instead of “Embedded”. I tested this by opening one of the Word doc.s and changing it. When I went back to the database and pulled up a view it reflected the change that was done outside of the database to the doc.
I have noticed that many people use a pathname in varchar but am not sure how that would work with the Access Views and reports. Maybe I should have said that I don’t know how to make that all work together.
Sounds like I should take another look at my indexes.
Oh, what is a “Powerbuilder application”? How does it help? Name brand?
Thanks,
Bryan
May 21, 2004 at 10:03 am
Powerbuilder is a development environment/language produced by Sybase, Inc. (from whom Microsoft used to license SQL Server code, through version 4.21a). My point was that I thought I did something similar to your app, but my databases simply store a binary copy of the document, not an embedded or linked object such that you get when you use Access. The object package seems to take up between 21-23 kb. Run a query on the table containing the documents, and select DataLength(yourImageCol) to see how many bytes each object takes up in SQL Server. All the sizes will probably be similar, regardingless of the size of the actual document.
So, maybe your speed issues are related to object activation.
What kind of queries are you running? Are you doing keyword searches of the document text, or just retrieving other columns in the table?
May 21, 2004 at 1:53 pm
Mike,
A DataLength Query showed the Word Doc.s at over 5Mb! What the heck?
The stand-alone files are large but are typically around 200kb each. Why would they show as over 5Mb? Would this suggest an error in my DTS from Access?
Lots of question marks over here………..
May 24, 2004 at 9:13 am
You didn't mention previously you were using DTS. Are you connecting to an Access database, then transferring all of the data to SQL Server using a DTS package? Was that a one-time operation, or an ongoing process? It does sound like something went wrong. How did you create the package?
I don't use Access a lot, except to periodically import data from another organization.
To access the SQL Server data from Access, I assume you link to the SQL Server table ( File | Get External Data | Link Tables ). In Access, what shows up in the document column of the SQL Server table: OLE Object, MS Word Document, or something else?
What happens when you double click on a row entry?
Mike
May 24, 2004 at 9:45 am
Mike,
I used DTS to do a “one time” data transfer from the Access backend to the SQL server.
Whether I use the Access front-end that is ODBC linked to SQL or the Access front-end that uses ADO, they both show “Word Document” in the field just as they did when attached to an Access Back-end. Of course, in the design of the table, it was referred to as an OLE object.
SQL server refers to it as an “Image” and any view in SQL returns the field as “binary”
I know the Word Doc.s are linked because I have tested this with performing edits on the document outside of the database. I don’t understand why the linked documents are so inflated. I have been thinking about attaching the Doc.s using SQL code, stored proc etc… (I have no clue and will have to look this up) instead of Access or DTS from Access.
May 24, 2004 at 2:22 pm
Maybe looking at datalength on a standard .GIF file would help me understand if I have a linking problem. When I link a 20KB .GIF file and then run a DATALENGTH on the column. My results show the file as 1.9MB. Is this normal?
May 26, 2004 at 6:34 am
I think you have to look how to make Path-through query in Access (so you make it on SQL Server directly)
and in this path through query you can write (with (nolock) ) in table hint. so you get data without making any lock on the server. (search for nolock in SQL Server Books online)
I hope this help u.
Alamir Mohamed
Alamir_mohamed@yahoo.com
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply