Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

First forgive me, I am not a DBA and our company really doesnt have one. I am a developer and Googling for answers is just not helping any more. Thanks in advance.

My question is on optimal structure for our database. We have a website that allows users to search and find PDFs easily. Mostly its just meta data searching but we do allow full text searching (All words, Any Words, Exact Phrase).

There are ~3 million PDFs (growing daily) at about 2.3 TB in size. These are stored in tables corrisponding to date (File1980, File1990, File2000, etc) using VARBINARY(MAX). We didnt make it a single table because our system admin was having trouble with backups. Each of the file tables (File1990,etc) has it's own MDF file.

Our website is using Microsoft Dynamic Data and Entity framework. We search across the PDFs using one of the table value functions below that joins with the rest of the LINQ query.

ALTER FUNCTION [dbo].[udf_ContainsSearch]   (@keywords nvarchar(4000))
RETURNS @containsTable TABLE
(
    [key] [uniqueidentifier] NOT NULL, --PRIMARY KEY NONCLUSTERED,
    [rank] [int] NOT NULL
    PRIMARY KEY NONCLUSTERED ([key], [rank])

)

AS
BEGIN
    insert into @containsTable 

    select [key],[rank] from ContainsTable(File2010,Content,@keywords)
    union  
    select [key],[rank] from  ContainsTable(File2000,Content,@keywords)
    union    
    select [key],[rank] from  ContainsTable(File1990,Content,@keywords)
    union    
    select [key],[rank] from  ContainsTable(File1980,Content,@keywords)
    union    
    select [key],[rank] from  ContainsTable(File1970,Content,@keywords)

    RETURN
END

ALTER FUNCTION [dbo].[udf_FreetextSearch]   (@keywords nvarchar(4000))
RETURNS @containsTable TABLE
(
    [key] [uniqueidentifier]  NOT NULL, --PRIMARY KEY NONCLUSTERED,
    [rank] [int] NOT NULL
    PRIMARY KEY NONCLUSTERED ([key], [rank]) 
)

AS
BEGIN
    insert into @containsTable 

        select [key],[rank] from FreetextTable(File2010,Content,@keywords)
        union  
        select [key],[rank] from  FreetextTable(File2000,Content,@keywords)
        union    
        select [key],[rank] from  FreetextTable(File1990,Content,@keywords)
        union    
        select [key],[rank] from  FreetextTable(File1980,Content,@keywords)
        union    
        select [key],[rank] from  FreetextTable(File1970,Content,@keywords)

    RETURN
END

We have been getting timeout issues on the web site and have been looking into performance. Running the database tuner and adding some indexes on a weeks worth of queries helped but not enough.

Now the questions:

Is there a better way to structure the data that would give better performance?

We use professional SQL Server 2008, would Enterprise help us in anyway?

Is there a better way for the full text search portion?

share|improve this question
    
Why build this yourself? Aren't there (possibly even open source) PDF indexing solutions out there? –  Colin 't Hart Feb 20 at 15:49
    
I inherited the system. My issue with moving to a different solution would be using resources and then being in the same or even worse situation. (not that I am against it). Id' take suggestions on products like this if anyone has experience with them. –  dz15 Feb 20 at 16:15
    
You are addressing this the wrong way. You should use FullText Catalog. technet.microsoft.com/en-us/library/ms142571.aspx AND you should avoid User Defined functions because it it difficult to calculate performance out of it. You should use Stored Proc. –  PollusB Feb 20 at 19:18
    
Each file has its own FullText Catalog. Also I believe the UDF is used over the SP because the result of the UDF is then joined with the rest of the query in EF. –  dz15 Feb 20 at 20:10
1  
Can you show us the context in which the TVF is used? You could try changing it to an inline TVF, which will inject the query into the caller instead of calling it like a black box. This may help performance, and also may give insight into a perf problem because the contents of the TVF will show up in the query plan (which would also be helpful to see). Have you considered caching search results for frequently-searched keywords? Which portions of the data are read-only, if any? –  Jon Seigel Feb 21 at 19:08

1 Answer 1

up vote 0 down vote accepted

Did you try to replace the insert..union statements with separate insert statements?

For example:

insert into @containsTable select [key],[rank] from ContainsTable(File2010,Content,@keywords)
insert into @containsTable select [key],[rank] from ContainsTable(File2000,Content,@keywords)
insert into @containsTable select [key],[rank] from ContainsTable(File1990,Content,@keywords)
insert into @containsTable select [key],[rank] from ContainsTable(File1980,Content,@keywords)
insert into @containsTable select [key],[rank] from ContainsTable(File1970,Content,@keywords)   

It could be as simple as the increased processing cost of union plus the increased memory usage from having 5 processes open at the same time to use them on that insert, slowing down your UDF.

(Also, since you're querying from - supposedly - unrelated data tables, the 'distinct' part of union should be unnecessary.)

share|improve this answer
    
This alone did not make any difference but it did bring my attention to the fact that all five tables are searched whether needed or not. (Obvious fix that just didtn jump out before). That and I have a few other ideas. Thanks for shaking my brain. –  dz15 Feb 25 at 14:50

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.