The Old Joel on Software Forum: Part 4 (of 5) - converted sql made on-the-fly to param queries

converted sql made on-the-fly to param queries

At work I had to write an application in which every single bit of SQL needed to have one chunk of SQL in common.

So the SQL was generated on the fly by string concatentation at run-time. This is a Visual Basic 6 application that connects to a Microsoft Access/Jet database.

It all works fine.

The manager very loosely connected with the project wasn't happy with SQL created on the fly.

So I converted all the SQL made up on the fly into parameterized views that are made once during database creation.

However the application is now actually slower!!!! Before it took about a second to load and display a set of linked rows. Now it takes about 3 seconds.

So it seems parameterized queries are slower than making up SQL on the fly at runtime. I would have thought the opposite to be the case.

By the way this is what I'm using to create each parameterized view.

Private Sub mdb_Query_Create(sQName As String, sSQL As String, objConn As adodb.connection, _
objCat As ADOX.Catalog)
Dim cmd As New adodb.Command

cmd.CommandText = sSQL
objCat.Views.Append sQName, cmd

Set cmd = Nothing
End Sub

savage
Monday, March 29, 2004

The saved queries are faster if you create them before-hand and *save* them. The database then has a chance to compile them, run a query plan, etc. (which I assume even Access does). However, they are certainly NOT going to be faster if you are creating them on-the-fly!

Jordan Lev
Monday, March 29, 2004

The parameterized queries are saved in the database and are persistent. That is why I am surprised.

savage
Monday, March 29, 2004

Something is wrong.

If your DB doesn't actually support parameters (Access???) then the ODBC driver is going to be doing the heavy lifting.

You suuuuuure you're not creating the command every time you run it?

There could be some expensive type massaging going on when using parameters (like String-to-Date) that used to be hidden on the DB side when passing everything as text to the DB.

Richard P
Monday, March 29, 2004

From your code, I don't see where you're using parameters or re-using queries at all. It looks like you're still creating a new Command based on a String for each query.

Richard P
Monday, March 29, 2004

Sorry, the code that makes the query and runs it to get the recordset is below.

I'm guessing at the moment that to get the time saving I have to keep the Command object rather than make a new one each time.

Dim strSQL As String
Dim strSQLPropCharSubObject As String
Dim strSQLFinSrcSubObject As String
Dim objCn As adodb.connection
Dim objRs As adodb.recordset
Dim vecStRetVal As Boolean
Dim objCat As ADOX.Catalog
Dim objCmd As adodb.Command

If (baseID = vbNullString) Then
Err.Raise 1, "NScenario::LoadFromDatabase", "Argument BaseID is empty"
End If

m_BaseID = baseID
vecStRetVal = True

Set objCn = GetDbConnection

Set objCat = New ADOX.Catalog
Set objCat.ActiveConnection = objCn
Set objCmd = objCat.Procedures(Q_SUPPLIEDBASEID_SCENARIO).Command

'Set objRs = objCn.Execute(strSQL)
Set objRs = objCmd.Execute(, Array(m_BaseID))

m_ID = objRs.Fields.Item("ID")
m_Seq = objRs.Fields.Item("Seq")
m_NotesID = objRs.Fields.Item("NotesID")

Savage
Monday, March 29, 2004

Savage,

1. http://peach.ease.lsoft.com/scripts/wa.exe?A0=visbas-l might be a better forum for this question.
2. You need to show us the original code you were using. There is no point just showing us the new code - we need to see a 'before and after' image.
3. You need to identify the bottleneck. To do this, use GetTickCount ( http://www.codehound.com/vb/results/results.asp?S=1&Q=gettickcount ). Put timing code in both the new code and the old code. Compare it. Identify the bottleneck (since it is likely not what you think it is).
4. I have some other suggestions that will probably help, but will wait for your feedback from 3 above before I send you on a wild goose chase.

Seeya

Tuesday, March 30, 2004