So, my mates machine with XP Home on started crashing on him. Me being a bad friend, I failed to go round in time to stop him formatting the whole drive. He continued to get the error, which was on boot -
Unmountable_boot_voume
***STOP: 0x000000ED (0x82B6A900, 0xC0000185, 0x00000000, 0x00000000, 0x00000000)
Then you'd get random NTFS errors or NTLDR missing etc.
I removed the new RAM he'd just bought and all fixed. Put the RAM back in and you got the Pac-Man noise. He'd googled for it but got no joy, so I thought I'd post this up in case anyone else gets fried in the same way.
On one of our new sites (Still in development) I needed a javascript slideshow of the hotel images. Problem was, the back button held onto each flip through, so to return to the previous page involved a number of hits on the back button.
I looked into the history object, replacing it and generally mucking about with it but after much messing about I discovered a hack which works nicely for me.
I used an IFrame to display the slideshow page, and used an alternate domain name for that page - eg, we have www.travelcounsellors.com and www.travelcounsellors.co.uk. Because the IFrame referenced www.travelcounsellors.com, it didn't hold the history of co.uk so happy days :). I then thought, hey, what if its not an ASP.NET page, but just an asp page in the same domain? Then it would be part of a different application? That works too...
Here's what I mean
With regards to SQL-Injection attacks, I wasn't too concerned as it was a windows service I was writing. However, the poke about performance got me thinking. Would it really be any quicker with parameters, when I was just calling a sproc a few times.
Kirk Allen Evans (Good book btw) had these points to make
Second, you will get better performance by using a parameterized SQL statement by replacing the literal values with question marks and using parameters in place of the literal values. The parameters can then be reused as well, where you simply set the values during each iteration.
Third, you can enhance performance a little more if your provider supports the .Prepared property for the Command object. The .Prepared property pre-compiles the SQL statement and caches it, avoiding parses for invaalid SQL statements.
So, I needed to convert this module to run in a more optimised way.
Sub Main()
Dim startTime As Date = Date.Now
Dim myConn As SqlConnection = New SqlConnection("server=xxx; user id=xxx; password=xxx; database=xxx")
myConn.Open()
Dim main_startDate As Date = Date.Today.AddDays(5)
Dim startDate As Date = Date.Today.AddDays(5)
Console.WriteLine("Today=" & startDate.ToString("s"))
Dim mySQL As New SqlCommand("exec tcHolidays.dbo.TCHolidays_Get_Hotel_Costs 2,0,1,'" & startDate.ToString("s") & "',3,'','','Boston',9999,0", myConn)
mySQL.ExecuteNonQuery()
Dim resorts(4)
resorts(0) = "New York"
resorts(1) = "Orlando"
resorts(2) = "Miami"
resorts(3) = "Las Vegas"
resorts(4) = "Boston"
Dim resort As String
For Each resort In resorts
While startDate < Date.Today.AddDays(10)
mySQL.CommandText = "exec tcHolidays.dbo.TCHolidays_Get_Hotel_Costs 2,0,1,'" & startDate.ToString("s") & "',7,'','','" & resort & "',9999,0"
mySQL.ExecuteNonQuery()
mySQL.CommandText = "exec tcHolidays.dbo.TCHolidays_Get_Hotel_Costs 2,0,1,'" & startDate.ToString("s") & "',10,'','','" & resort & "',9999,0"
mySQL.ExecuteNonQuery()
mySQL.CommandText = "exec tcHolidays.dbo.TCHolidays_Get_Hotel_Costs 2,0,1,'" & startDate.ToString("s") & "',14,'','','" & resort & "',9999,0"
mySQL.ExecuteNonQuery()
mySQL.CommandText = "Exec tcHolidays.dbo.TCHolidays_get_Flights '" & startDate.ToString("s") & "','" & resort & "'"
mySQL.ExecuteNonQuery()
startDate = startDate.AddDays(1)
Console.WriteLine("Done flights and Hotels for " & startDate.ToShortDateString() & " in " & resort & ".")
End While
startDate = main_startDate
Next
myConn.Close()
Dim endTime As Date = Date.Now
Dim timeSpan As TimeSpan = endTime.Subtract(startTime)
Console.WriteLine("Process took " & timeSpan.ToString() & ".")
Console.WriteLine("Press enter..")
Console.ReadLine()
End Sub
Doing it this way took 1.21.044 (m.s.ms)
To answer Kirks first point, I needed to parametise two stored procedures. I did this outside of the main loop, meaning I could reuse the parameters as suggested.
Dim mySQL As SqlCommand = New SqlCommand
With mySQL
.CommandText = "tcHolidays.dbo.TCHolidays_get_hotel_costs"
.CommandType = CommandType.StoredProcedure
.Connection = myConn
.Parameters.Add(New SqlParameter("@num_Adults", SqlDbType.Int))
.Parameters("@num_Adults").Value = 2
.Parameters.Add(New SqlParameter("@num_Children", SqlDbType.Int))
.Parameters("@num_Children").Value = 0
.Parameters.Add(New SqlParameter("@num_Rooms", SqlDbType.Int))
.Parameters("@num_Rooms").Value = 1
.Parameters.Add(New SqlParameter("@theDate", SqlDbType.VarChar, 20))
.Parameters.Add(New SqlParameter("@nights", SqlDbType.Int))
.Parameters.Add(New SqlParameter("@hcode", SqlDbType.VarChar, 30))
.Parameters("@hcode").Value = ""
.Parameters.Add(New SqlParameter("@country", SqlDbType.VarChar, 30))
.Parameters("@country").Value = ""
.Parameters.Add(New SqlParameter("@resort", SqlDbType.VarChar, 30))
.Parameters.Add(New SqlParameter("@maxPrice", SqlDbType.Int))
.Parameters("@maxPrice").Value = 9999
.Parameters.Add(New SqlParameter("@minPrice", SqlDbType.Int))
.Parameters("@minPrice").Value = 0
End With
Inside the main loop, I then simply change the values of those parameters which change inside the loops:
With mySQL
.Parameters("@theDate").Value = startDate.ToString("s")
.Parameters("@nights").Value = 7
.Parameters("@resort").Value = resort
.Prepare()
.ExecuteNonQuery()
End With
Because I was reusing the mySQL.Command - I needed another sqlCommand object for the last sproc, which obtains flights.
With myFlight_SQL
.Parameters("@theDate").Value = startDate.ToString("s")
.Parameters("@resort").Value = resort
.Prepare()
.ExecuteNonQuery()
End With
Now. Doing it this way took 1.28.088... 4 seconds slower.
So, I extended it to run over a longer period (30 days, rather than 5). The results were much the same.
With parameters: 8.52.9988944
Without - 8.10.13825644
Can anyone tell me where my extra 40 seconds is coming from?
The upshot is that doing it parametized is *much* cleaner code. I can hand this console to anyone and they'll be able to see instantly what it is doing, and why. Downside is that it seems to be slower but I think that the extra sqlCommand has somethign to do with that.
Due to a freaky table schema, I had cause today to loop through a 'recordset' and then insert an amalgamated string into a database (saving me doing a ridiculous loop later on).
I didn't see any reason to open a new SQL command once the datareader had done its job so I re-used the SQLCommand - which turned out a lot easier than I thought. Certainly a ton easier than the old recordset. After using the imports for data and data.sqlClient I simply:
Function get_description(ByVal hcode As String)
Dim myQry As String = "SELECT field1 from table where hcode='" & hcode & "'"
Dim mytemp_Conn As SqlConnection = New SqlConnection(myConn)
mytemp_Conn.Open()
Dim myCmd As New SqlCommand(myQry, mytemp_Conn)
Dim myDr2 As SqlDataReader
Dim Description As String = "" ' Description of the hotel
Dim tempString As String = ""
myDr2 = myCmd.ExecuteReader()
While myDr2.Read()
tempString = myDr2.Item(0) & " "
tempString = tempString.Replace("'", "''")
Description &= tempString
' Concatenate each of the freaky fields.
End While
myDr2.Close()
myQry = "Insert into tcHolidays.dbo.hotel_Descriptions (hcode, description) values ('" & hcode & "','" & Description & "')"
myCmd.CommandText = myQry
myCmd.ExecuteNonQuery()
mytemp_Conn.Close()
Console.WriteLine("Description: {0}", Description)
End Function
That was basically it! In classic I'd have had to have opened a new recordset - but now its simply wash-n-go :)
Our new sites have been up and running now for about 8 weeks. They were submitted in normal fashion, and I have not done any sneaky Google tricks like Bombing, NoText-madness or whitespacing. In fact, most of the pages aren't really well optimised. We've previously had good results with Google, achieving top 10 results for some terms and top 1 for a few select ones, which is a big achievement in travel term (We think).
Anyway, on the last pass by the googlebot - we got a PR of...ZERO!! Nightmare I cried. Desperate to discover why, I checked the sites, checked for back links, used my toolbar to maximum effect but could still not decipher why we had a PR of Zero, which in effect kills your rankings.
<As_I_Understand_It> In very short form, the PR is a modifier to your google score. You could get so many points for content, keyword positioning etc etc. Say you got an arbitrary score of 500 for your page. Your PR is derived from the PR of those pages that link to this page. if your PR is say 4 (And the PR thing is really just a nominal number plucked out of the ether) then your ranking score is 2000. If your PR was 8, your ranking score would be 4000, etc. </As_I_Understand_It>
Google grabs all those links that have a certain ranking score at the end and then displays so many over a certain amount. The higher your PR, the better your chance of getting a good ranking with a poorly designed page. With a PR of 0, you have basically no chance of being up with the bad boys of SEO
Now, I'm expecting, nay, hoping, that someone will tell me different.Anyway, after much work , I found that the toolbar was being inconsistent. A page with a zero page rank was indeed still showing up in the top 10 for its term. So it *couldn't* have had a PR of 0. No way, uh uh...
A mooch on the search engine forums reveals that what is happening is that the google toolbar is being worked on, hence its exclusion from content heavy Google main page.It even led to the Aprils fools joke about PigeonRank
For completeness, the sites are:
Travel Home Working
Travel Counsellors
Recruitment Counsellors