Web Host MS Sql Performance - My Fault?

Hi,

I'm using SqlServer 2000 at a web host, and share this db with about 200 others (it's an inexpensive web host so I guess there is not very much traffic on most of them, but I can only see their db IDs so I don't know what organisations they are or the urls they have).

Usually, my web site runs fine, but lately it's really really slow (as in non-functioning in reality). I'm in the middle of coding my seach functionality, so I fear that I'm causing this myself. ;-)

However, there are only ten posts in my test db, so it should be a piece of cake for SqlServer. The other .aspx pages runs rather slow too now, but when I start a programming session they have worked fine (this has happened three times now so I don't know what to conclude). When the server slows down, it can stay down for hours. I've also tried to access my site from other computers but get the same result, so I guess it has nothing to do with caching (I also tried emptying my browser's - IE7 - cache). I have several asp web sites with Access dbs at the same web host, and they all work fine. In addition, I have no problem viewing and editing my db at the web host via SqlServer Management Studio Express.

What do you think of this problem? Should I go for a more expensive web host? Should I give them the "It's not you, it's me" speech? ;-) Or is it really me?

Below's my search code, and as you can see, there are some integers there to make sure the loops don't run ad infinitum. I don't really think the problem actually lies with my search code, as everything worked well a week ago, but I don't know...

BTW, if you think my code can run much faster without getting too tricky, please let me know.

Please help!

Pettrer

If Page.IsPostBack Then

Dim soktext As String = Trim(SokTextBox.Text) + " "

Dim tillagg As String = ""

Dim varv As Integer = 1

Dim loopvarv As Integer = 0

soktext = Replace(soktext, vbCrLf, " ") 'vbCrLf = mellanslag i VB-terminologi

Do While InStr(soktext, " ") And varv < 100

soktext = Replace(soktext, " ", " ")

loopvarv += 1

Loop

While Trim(soktext).Length > 0 And varv <= 20 'Loop för att identifiera ord och lägga till dem i sqlsatsen, ett i taget

Dim icke As String = " "



Dim ordet As String = soktext.Substring(0, soktext.IndexOf(" ")) 'Det godkända ordet läggs till i tmp-strängen

soktext = soktext.Remove(0, soktext.IndexOf(" ") + 1) 'Tar bort det som just använts (eller varit för kort)

If ordet.Length > 1 Then 'Blanksteg och ensamma bokstäver förhindras att komma med i sql-satsen

If ordet.StartsWith("-") Then 'Det är ett minusord

icke = " NOT " 'I sqlsatsen blir det alltså AND NOT (...)

ordet = ordet.Remove(0, 1) 'Tar bort minusttecknet från söksträngen

End If

tillagg += " AND " & icke & " (artistnamn LIKE '%" & ordet & "%' OR " & _

"beskrivning LIKE '%" & ordet & "%' OR " & _

"kulturhemvist LIKE '%" & ordet & "%' OR " & _

"kontaktnamn LIKE '%" & ordet & "%') "

End If

varv += 1

End While

Dim kat As String = ""

If KategoriDropDownList.SelectedValue <> 0 Then

kat = " AND (kategori1 = " & KategoriDropDownList.SelectedValue & _

" OR kategori2 = " & KategoriDropDownList.SelectedValue & _

" OR kategori3 = " & KategoriDropDownList.SelectedValue & _

" OR kategori4 = " & KategoriDropDownList.SelectedValue & ") "

End If

Dim lan As String = ""

If LanDropDownList.SelectedValue <> "alla" Then

lan = " AND lan = '" & LanDropDownList.SelectedValue & "' "

End If

ResultatDataSource.SelectCommand = "SELECT DISTINCT TOP 200 folknuID, kategori1, kategori2, kategori3, kategori4, initial, artistnamn, lan, kontakthemsida, kategori1, (COALESCE(audio1,'') + COALESCE(audio2,'') + COALESCE(audio3,'')) As audio, (COALESCE(video1,'') + COALESCE(video2,'') + COALESCE(video3,'')) As video, (COALESCE(pressbild1,'') + COALESCE(pressbild2,'') + COALESCE(pressbild3,'')) As pressbild " & _

"FROM Mytable " & _

"WHERE shown = 1 AND shownadm = 1 " & _

tillagg & _

kat & _

lan & _

"ORDER BY initial"

ResultatLabel.Text = "SelectCmd: " & ResultatDataSource.SelectCommand

ResultatGridView.DataBind()

End If '*** på sökkoden