Gammon Forum : MUSHclient : General : Question about SQL specifically nrows

Entire forum ➜ MUSHclient ➜ General ➜ Question about SQL specifically nrows

Question about SQL specifically nrows

It is now over 60 days since the last post. This thread is closed. Refresh page

Pages: 1 2

Posted by Teclab85 (16 posts) Bio

Date

Reply #15 on Wed 11 May 2016 09:30 PM (UTC)

Amended on Thu 12 May 2016 12:15 AM (UTC) by Teclab85

Message


function getRoomIdRoomCP(name, nameHolder, tableNum)
local loc = cp_mobs[tableNum].location
local mob_table_name= ''
local rows_counter= 1
local rows_counter_check= 1
local inTable = false
local inLevel = true
res, gmcparg = CallPlugin("3e7dedbe37e44942dd46d264","gmcpval","char")
luastmt = "gmcpdata = " .. gmcparg
assert (loadstring (luastmt or "")) ()
level = tonumber(gmcpdata.status.level) 
min_level = level - 11 
max_level = level + 11 
if (min_level<0)then min_level=0 end
if not dbkt:isopen() or not dbA:isopen() then
  return
end--if

query1 = string.format("select rooms.uid,"..
  " areas.name as areaName,"..
  " rooms.name as roomName"..
  " from rooms, areas"..
  " where areas.uid = rooms.area and rooms.name = %s",fixsql(cp_mobs[tableNum].location))
for rows in dbA:nrows(query1) do
  rows_counter = rows_counter+ 1
end--for
DebugNote(rows_counter)
for rows in dbA:nrows(query1) do
  rows_counter_check = rows_counter_check+1
  query = string.format("select room_id, COUNT(*) as timeskilled,"..
    " name from mobkills "..
    "where room_id = %s "..
    "and lower(name) = %s"..
    " group by room_id, name "..
    "ORDER by timeskilled asc ", fixsql(rows.uid), fixsql(string.lower(name)))
  for a in dbkt:nrows(query) do     
    roomTemp= a.room_id
    if string.lower(a.name) == string.lower(name) then
      roomNumber= roomTemp
      mob_table_name = a.name      
    end --if
  end--for
      if areaLevel[rows.areaName].minLevel <= max_level and areaLevel[rows.areaName].maxLevel>=min_level then 
        inLevel = true
        if room_num_table ~= nil and #room_num_table > 0 then
          for i, v in ipairs (room_num_table) do
            DebugNote(v[2])
            DebugNote(nameHolder)
            DebugNote(tableNum)
            DebugNote(tableNumHolder)
            if ((string.lower(v[2]) == string.lower(nameHolder) or v[1] == rows.uid) and tableNum == tableNumHolder) then
              inTable = true
            end--if
          end--for
        end--if
        if mob_table_name~= '' and inTable == false then
          makeTable(roomNumber, cp_mobs[tableNum].name, cp_mobs[tableNum].mobdead, true)
          mob_index= 1
          DebugNote("first if part")
          rows_counter= rows_counter-1
        rows_counter_check = rows_counter_check-1
        elseif inTable == false then
          makeTable2(tonumber(rows.uid), cp_mobs[tableNum].name, cp_mobs[tableNum].mobdead, false)
          mob_index= 1
          DebugNote("Second if part")
          DebugNote(rows.uid.. " " .. cp_mobs[tableNum].name)
          rows_counter= rows_counter-1
        rows_counter_check = rows_counter_check-1
        end--if
      else
        rows_counter= rows_counter-1
        rows_counter_check = rows_counter_check-1
        inlevel = false
      end--if
end--for
  
  if rows_counter_check == rows_counter and inTable== false and inlevel == true then
    makeTable(-1, cp_mobs[tableNum].name, cp_mobs[tableNum].mobdead, false)
    print ('-1 becuase check = counter')
  end--if
  tableNumHolder= tableNum
end

So the issue is that with this it takes much longer, 5 or so seconds. I didn't see any actual issues with the old code, aside from some stuff that I needed to do better like the area checker that needed to be not a for loop as the array would work to use it.

I realize this is dirty looking and probably not efficient. This was my first plugin that I ever wrote and I have been slowly converting it over to a decent program for two years now in my spare time.

Top

Posted by Nick Gammon Australia (23,173 posts) Bio Forum Administrator

Date

Reply #16 on Thu 12 May 2016 03:55 AM (UTC)

Amended on Thu 12 May 2016 03:56 AM (UTC) by Nick Gammon

Message

For a start, this is very inefficient:


query1 = string.format("select rooms.uid,"..
  " areas.name as areaName,"..
  " rooms.name as roomName"..
  " from rooms, areas"..
  " where areas.uid = rooms.area and rooms.name = %s",fixsql(cp_mobs[tableNum].location))
for rows in dbA:nrows(query1) do
  rows_counter = rows_counter+ 1
end--for

If you are just counting things that match a criteria, get a count:


query1 = string.format("SELECT COUNT(*) AS counter " ..
  " FROM rooms, areas"..
  " WHERE areas.uid = rooms.area AND rooms.name = %s",fixsql(cp_mobs[tableNum].location))
for rows in dbA:nrows(query1) do
  rows_counter = rows.counter
end--for

That returns a single row, which is the count of how many records match the "where" clause.

And here:


        query = string.format("select room_id, COUNT(*) as timeskilled,"..
          " name from mobkills "..
          "where room_id = %s "..
          " group by room_id, name "..
          "ORDER by timeskilled asc ", fixsql(rows.uid))
        for a in dbkt:nrows(query) do   
          roomTemp= a.room_id
          if string.lower(a.name) == string.lower(name) then
            roomNumber= roomTemp
            mob_table_name = a.name
            break       
          end --if
        end--for

You are looking for a certain name, why not make that part of the select?


where room_id = %s AND name = %s

Now you don't need to break out of the loop.

- Nick Gammon

www.gammon.com.au, www.mushclient.com

Top

Posted by

Teclab85 (16 posts) Bio

Date

Reply #17 on Fri 13 May 2016 10:20 AM (UTC)

Message

As I don't have much time to work on this project it is going slowly. I did convert the stupid thing I did with cycling through a whole table to get the count over to just using Count(*) and reading in the counter. Which by the way thank you, that was a terrible rookie mistake.

On to the rest.

I did add in searching for the where name = %s (name of mob) but that actually doesn't work as well as you would think, still returns 1-150 entries, helpful but not perfect yet. As it is down from 200-500 entries.

The whole conversion is still much slower than breaking out of the calls.

I am more interested in why I am not seeing the lockups or any errors for breaking or returning out of these calls.

I am thinking it might have something to do with the fact that I am using collectgarbage("collect") Before and after the program does what it is supposed to do. This would account for at least keeping the memory down, but not for locks on the .db files that I use.

I am calling into two different .db files to do one search and one of them is the Aardwolf.db file which if that was locked at any given time you would think the mapper would not work.

I just don't know. I am trying to do this the right way, but it is killing performance. I know you guys have given me a tremendous amount of resources to rectify this, but it just seems to keep slowing the program down.

Top

Posted by

Nick Gammon Australia (23,173 posts) Bio Forum Administrator

Date

Reply #18 on Fri 13 May 2016 10:59 AM (UTC)

Message

The method I suggested previously for breaking out of a loop after doing a select should work. After all you were doing that anyway. I don't know why it would suddenly add 5 seconds to the search.

500 records isn't much. Something else odd is going on.

- Nick Gammon

www.gammon.com.au, www.mushclient.com

Top

The dates and times for posts above are shown in Universal Co-ordinated Time (UTC).

To show them in your local time you can join the forum, and then set the 'time correction' field in your profile to the number of hours difference between your location and UTC time.

69,313 views.

This is page 2, subject is 2 pages long: 1 2

It is now over 60 days since the last post. This thread is closed. Refresh page

Go to topic: Search the forum

top