Prev: Making table filters sticky
Next: How to Migrate Reporting Services Reports to new Server
From: ptvvee on 12 Aug 2010 13:44
Hi

I have a C# web application that connects to a SQL Server database. I
have about 15000 records where there is a resume field for each
record. I have a SQL SELECT (using LIKE) statement that the C#
creates based on users input to search for a resume that has whatever
keyword(s) the user would like to find. However, for some reason, the
searches come up blank the majority of the time, even when I am
searching using words that I know are in the database records. I have
this application and database hosted on a GoDaddy server.

It seems like the page is waiting for the response from the database,
but if the response isn't back soon enough, the page just goes ahead
and renders (but with no found records).

Is this something that I can fix by telling the C# page to wait until
a response is received back from the SQL Server database? Or could
this be a setting in SQL Server I don't have set correctly? Or
perhaps it is an issue with the host company?

Thanks for anybody's help in pointing me in the right direction.

Paul
From: Erland Sommarskog on 12 Aug 2010 18:16
ptvvee (jobs(a)thomassharp.com) writes:
> I have a C# web application that connects to a SQL Server database. I
> have about 15000 records where there is a resume field for each
> record. I have a SQL SELECT (using LIKE) statement that the C#
> creates based on users input to search for a resume that has whatever
> keyword(s) the user would like to find. However, for some reason, the
> searches come up blank the majority of the time, even when I am
> searching using words that I know are in the database records. I have
> this application and database hosted on a GoDaddy server.
>
> It seems like the page is waiting for the response from the database,
> but if the response isn't back soon enough, the page just goes ahead
> and renders (but with no found records).
>
> Is this something that I can fix by telling the C# page to wait until
> a response is received back from the SQL Server database? Or could
> this be a setting in SQL Server I don't have set correctly? Or
> perhaps it is an issue with the host company?

It's very unclear what your problem is. You say your pages come up "blank".
Does this mean that they do not display anything at all? Or do they
display like "no records found"? What you get these "blank" pages, how
long does it take? Do searches for some keywords come back with data and
sometimes come up "blank"?

How does your SELECT statements look like?


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: ptvvee on 12 Aug 2010 22:28
On Aug 12, 6:16 pm, Erland Sommarskog <esq...(a)sommarskog.se> wrote:
> ptvvee (j...(a)thomassharp.com) writes:
> > I have a C# web application that connects to a SQL Server database.  I
> > have about 15000 records where there is a resume field for each
> > record.  I have a SQL SELECT (using LIKE) statement that the C#
> > creates based on users input to search for a resume that has whatever
> > keyword(s) the user would like to find.  However, for some reason, the
> > searches come up blank the majority of the time, even when I am
> > searching using words that I know are in the database records.  I have
> > this application and database hosted on a GoDaddy server.
>
> > It seems like the page is waiting for the response from the database,
> > but if the response isn't back soon enough, the page just goes ahead
> > and renders (but with no found records).
>
> > Is this something that I can fix by telling the C# page to wait until
> > a response is received back from the SQL Server database?  Or could
> > this be a setting in SQL Server I don't have set correctly?  Or
> > perhaps it is an issue with the host company?
>
> It's very unclear what your problem is. You say your pages come up "blank".
> Does this mean that they do not display anything at all? Or do they
> display like "no records found"? What you get these "blank" pages, how
> long does it take? Do searches for some keywords come back with data and
> sometimes come up "blank"?
>
> How does your SELECT statements look like?
>
> --
> Erland Sommarskog, SQL Server MVP, esq...(a)sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000:http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Hi Erland... thank you for the response. I know my issue is a little
unclear but that is what is so frustrating. I have tested and used
this application locally and on the web with smaller amounts of data
and it works fine. I have no way to test it on the web host though to
see what is happening (as all of the data is there). Basically
though, the SELECT statement looks something like this :

Select * from tblCandidates WHERE Resume LIKE Java (Sorry if the
syntax is a little off)

The page that renders the results of the search are supposed to take
all of the records and format them into a list. So if 5 resumes had
the word Java, it would look something like :

John Jones
Peter Smith
Greg Johnson
Eric Ravul
Betsy Hamilton

where each of these would be links, that when you click them show
their resume.

To answer a couple of your questions though, when the pages come back,
the header info is there, there are just no records. And yes, when it
does this, it takes a long time. And yes, sometimes when you put in a
search word it comes back with results where other times the exact
same search shows no results. Sometimes you have to do the same
search 2-10 or more times and then it will finally show records
(like.. when you know that a word exists in resumes... something
common like Java).

Does this have anything to do with the size of the database? And is
there a way to tell the C# page to wait until the results come back
(if this is what is happening)?

Thanks for all of your help.... I appreciate the links but they aren't
going to help someone like me who isn't a SQL Server expert where this
seems to be an advanced problem.... thanks, Paul

From: Erland Sommarskog on 13 Aug 2010 04:51
ptvvee (jobs(a)thomassharp.com) writes:
> Select * from tblCandidates WHERE Resume LIKE Java (Sorry if the
> syntax is a little off)

Well, to be able to see if thre is any problem with the SQL, I would
need to see the exact statement. But from what you say that you get the data
sometimes, the SQL is probably OK.

> To answer a couple of your questions though, when the pages come back,
> the header info is there, there are just no records. And yes, when it
> does this, it takes a long time.

More exactly 30 seconds?

> Does this have anything to do with the size of the database? And is
> there a way to tell the C# page to wait until the results come back
> (if this is what is happening)?

It sounds that you get an command timeout. By default, ADO .Net cancels
running a query, if no result comes back from the server with the time
configured for the command timeout. Now, a command timeout raises an
exception, so show you manage to swallow that error. This is probably
something you should investigate first.

At home, take one of the guys that have Java in the r�sum� and do in a query
window:

BEGIN TRANSACTION
UPDATE tbl
SET firstname = 'Helge'
WHERE ...


and leave the transaction uncommitted. This should create the blank screen
in your test environment as well. If you debug this, you should see that
there is an exception, but what happens with it?

The CommandTimeout is a property, both on the Command and Connection
objects.

Since you are search for text in documents, you should probably consider
full-text searches for better speed. Preferrably, you want SQL 2008
for this.




--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 | 
Pages: 1
Prev: Making table filters sticky
Next: How to Migrate Reporting Services Reports to new Server