How to optimize visit counter SQL query?

Question

Using SQL server 2016 I have created the table VISIT with 3 columns ID,settoday,counter. This is the asp code that I use to count the daily visits. There are more than 2000 records in this table so far:

<%
'today is calculated on local calendar then:

sql="select * from visit where settoday='"& today &"'"
recordSet.open sql,objcon
if not recordSet.eof then

    sql="update visit set counter=counter+1 where id=" & recordSet("id")
    objcon.execute sql

else
    
    sql="insert into visit (settoday,counter) values ('" & today & "','1')"
    objcon.execute sql

end if
recordSet.close
%>

The ID column is primary key and also I have created an Index on settoday column. But When I check the Event profiler, The update query is taking a long time to execute. What more can I do to optimize the update code?

Warning, your code is open to injection attacks. **Parametrise** your queries. — Thom A, Feb 03 '21 at 15:37
Yes I mentioned than ID is primary key and settoday is Indexed. @Charlieface — Ali Sheikhpour, Feb 03 '21 at 15:39
Please share the query plans for these queries via https://pastetheplan.com — Charlieface, Feb 03 '21 at 15:44
Why you need the index on the `settoday` column ? This can slow down any update on the table — GuidoG, Feb 03 '21 at 15:51
are the statistics up to date? RDBMS believing the table is nearly empty sometimes ignore alll indexes — Turo, Feb 03 '21 at 15:51
and have a look here: https://stackoverflow.com/questions/108403/solutions-for-insert-or-update-on-sql-server , I presume you want an update if exists — Turo, Feb 03 '21 at 15:56
I have created Index on settoday for the first select query among 2000 records. Doen't help? @GuidoG — Ali Sheikhpour, Feb 03 '21 at 15:59
Always think before you create an index. The can help performance when searching, but they can slow down update and insert statements. In the code you have shown I dont think you need that index. But I don't know what other code you have off course — GuidoG, Feb 03 '21 at 16:03
Thank you. There is no update on `settoday` column. It is just inserted once a day. Does it still slows down the update on `counter` column?@GuidoG — Ali Sheikhpour, Feb 03 '21 at 16:26

score 1 · Answer 1 · edited Feb 03 '21 at 20:50

I think you can gain a lot by avoiding having to go to the database twice. Also you should always try to use placeholders and parameters rather than concatenate values etc. I think it's not that big of a problem here because I presume today is not a user-furnished value, but still.

2000 records isn't much at all but yes, having the index on settoday will help, especially as the table grows. If your current version is slow then I suspect the network overhead is more to blame than the actual execution of the queries.

Rather than first get the ID for today and then deciding on whether you need to INSERT or UPDATE in asp you can do this in 1 single operation in SQL. It's been ages since I did this kind of code and I had to write it up in notepad so you'll probably have to fix some things here and there, but I think it's a start:

<%
sql = "DECLARE @today datetime = ?

UPDATE counter
   SET counter = counter + 1
 WHERE settoday = @today
 
 IF @@ROWCOUNT = 0
    BEGIN
        INSERT INTO visit (settoday, counter) values (@today, 1)"
    END"
    

set command = server.createobject("ADODB.Command")
set command.ActiveConnection = ...
command.QueryText = sql
command.CommandType = adCmdText
command.Parameters.Append(command.CreateParameter("@p1", adDate, adParamInput, today))
command.Execute

%>

How to optimize visit counter SQL query?

1 Answers1