On Sat, 24 Jan 2009 17:30:29 -0800 (PST), Wajih
<wajihur.reh...@gmail.com> wrote in General Discussion of
SQLite Database <sqlite-users@sqlite.org>:
>
>Hello,
>
>I recently wrote a software which decodes some binary data and then insert
>as a record into sqlite database. I check the code execution time and
>following are the results:
>
>Note: FLAG-2 indicates execution time of different
> procedure to decode data
> FLAG-3 shows the time to insert the data
No, it doesn't.
What do FLAG-4 and FLAG-5 indicate?
> Number in front of procedure name shows the time in micro seconds
Not in front of, but behind.
>Can any expert guide me where I am doing wrong?
Since your specifications aren't exact, we have to guess a
lot. So, just some notes for now:
- You don't use transactions. The database will perform much
better if you BEGIN before the first INSERT and COMMIT after
the last one. If there is just one INSERT, it doesn't
matter.
- The performance isn't bad. Total times are 0.169 and 0.158
seconds, repsectively. A blink of an eye. For real time
datacollection of call detail records (or for tail -f of the
primary call detail log) of a large switch it is too slow
indeed.
- One decoding procedure is a candidate for optimizing:
> Flag-2 HEADER FORMAT_VERSION 168443
but perhaps that is a reporting artefact?
- You have a lot of columns in your table. It may be
beneficial to use a large page size.
Hope this helps.
>Execution time:
>
>Flag-2 HEADER CHARGING_BLOCK_SIZE 49
>Flag-2 HEADER TAPE_BLOCK_TYPE 47
>Flag-2 HEADER DATA_LENGTH_IN_BLOCK 46
>Flag-2 HEADER EXCHANGE_ID 52
>Flag-2 HEADER FIRST_RECORD_NUMBER 59
>Flag-2 HEADER BATCH_SEQ_NUMBER 57
>Flag-2 HEADER BLOCK_SEQ_NUMBER 44
>Flag-2 HEADER START_TIME 105
>Flag-2 HEADER FORMAT_VERSION 209
>Flag-2 HEADER FORMAT_VERSION 168443
>Flag-2 Total Time 169642
>Flag-2 46
>Flag-2 PTC RECORD_NUMBER 101
>Flag-2 PTC RECORD_STATUS 46
>Flag-2 PTC CHECK_SUM 1569
>Flag-2 PTC CALL_REFERENCE 72
>Flag-2 PTC EXCHANGE_ID 19
>Flag-2 PTC INTERMEDIATE_RECORD_NUMBER 23
>Flag-2 PTC INTERMEDIATE_CHARGING_IND 43
>Flag-2 PTC NUMBER_OF_SS_RECORDS 23
>Flag-2 PTC CALLING_NUMBER_TON 113
>Flag-2 PTC CALLING_NUMBER 37
>Flag-2 PTC CALLED_NUMBER_TON 98
>Flag-2 PTC CALLED_NUMBER 35
>Flag-2 PTC OUT_CIRCUIT_GROUP 38
>Flag-2 PTC OUT_CIRCUIT 38
>Flag-2 PTC IN_CHANNEL_ALLOCATED_TIME 73
>Flag-2 PTC CHARGING_START_TIME 70
>Flag-2 PTC CHARGING_END_TIME 76
>Flag-2 PTC CAUSE_FOR_TERMINATION 1406
>Flag-2 PTC CALL_TYPE 65
>Flag-2 PTC TICKET_TYPE 48
>Flag-2 PTC OAZ_CHRG_TYPE 84
>Flag-2 PTC OAZ_DURATION 53
>Flag-2 PTC OAZ_TARIFF_CLASS 45
>Flag-2 PTC OAZ_PULSES 39
>Flag-2 PTC CALLED_MSRN_TON 103
>Flag-2 PTC CALLED_MSRN 35
>Flag-2 PTC INTERMEDIATE_CHRG_CAUSE 197
>Flag-2 PTC LEG_CALL_REFERENCE 59
>Flag-2 PTC OUT_CHANNEL_ALLOCATED_TIME 72
>Flag-2 PTC BASIC_SERVICE_TYPE 43
>Flag-2 PTC BASIC_SERVICE_CODE 272
>Flag-2 PTC CALL_REFERENCE_TIME 72
>Flag-2 PTC CUG_INTERLOCK 70
>Flag-2 PTC CUG_OUTGOING_ACCESS 65
>Flag-2 PTC CUG_INFORMATION 44
>Flag-2 PTC SCP_CONNECTION 47
>Flag-2 PTC NUMBER_OF_IN_RECORDS 23
>Flag-2 PTC NUMBER_OF_ALL_IN_RECORDS 23
>Flag-2 PTC OUTSIDE_USER_PLANE_INDEX 39
>Flag-2 PTC OUTSIDE_CONTROL_PLANE_INDEX 38
>Flag-2 PTC OUT_BNC_CONNECTION_TYPE 69
>Flag-2 PTC LOC_ROUTING_NUMBER 39
>Flag-2 PTC LOC_ROUTING_NUMBER_TON 96
>Flag-2 PTC NPDB_QUERY_STATUS 71
>Flag-2 PTC IN_CIRCUIT_GROUP_NAME 26
>Flag-3 PTC OUT_CIRCUIT_GROUP_NAME 20
>Flag-4 Insert time 151590
>Flag-5 Total Time 158077
>
>
>My Code is as below:
>This is one time statement and does account for the
>long time as the table was created once only
># PSTN Terminating Call
>db1 eval {CREATE TABLE PTC(
> RECORD_LENGTH text,
> RECORD_TYPE text,
> RECORD_NUMBER text,
> RECORD_STATUS text,
> CHECK_SUM text,
> CALL_REFERENCE text,
> EXCHANGE_ID text,
> INTERMEDIATE_RECORD_NUMBER text,
> INTERMEDIATE_CHARGING_IND text,
> NUMBER_OF_SS_RECORDS text,
> CALLING_NUMBER_TON text,
> CALLING_NUMBER text,
> CALLED_NUMBER_TON text,
> CALLED_NUMBER text,
> OUT_CIRCUIT_GROUP text,
> OUT_CIRCUIT text,
> IN_CHANNEL_ALLOCATED_TIME text,
> CHARGING_START_TIME text,
> CHARGING_END_TIME text,
> CAUSE_FOR_TERMINATION text,
> CALL_TYPE text,
> TICKET_TYPE text,
> OAZ_CHRG_TYPE text,
> OAZ_DURATION text,
> OAZ_TARIFF_CLASS text,
> OAZ_PULSES text,
> CALLED_MSRN_TON text,
> CALLED_MSRN text,
> INTERMEDIATE_CHRG_CAUSE text,
> LEG_CALL_REFERENCE text,
> OUT_CHANNEL_ALLOCATED_TIME text,
> BASIC_SERVICE_TYPE text,
> BASIC_SERVICE_CODE text,
> CALL_REFERENCE_TIME text,
> CUG_INTERLOCK text,
> CUG_OUTGOING_ACCESS text,
> CUG_INFORMATION text,
> SCP_CONNECTION text,
> NUMBER_OF_IN_RECORDS text,
> NUMBER_OF_ALL_IN_RECORDS text,
> OUTSIDE_USER_PLANE_INDEX text,
> OUTSIDE_CONTROL_PLANE_INDEX text,
> OUT_BNC_CONNECTION_TYPE text,
> LOC_ROUTING_NUMBER text,
> LOC_ROUTING_NUMBER_TON text,
> NPDB_QUERY_STATUS text,
> IN_CIRCUIT_GROUP_NAME text,
> OUT_CIRCUIT_GROUP_NAME text)}
>
>Insert Statement (in TCL):
>
>switch -exact -- $Variable_Record_Type {
>
>HEADER { db1 eval "INSERT INTO HEADER($Variable_Column_Name) VALUES
>($Variable_Column_Value)"}
>TRAILER { db1 eval "INSERT INTO TRAILER($Variable_Column_Name) VALUES
>($Variable_Column_Value)"
>MOC { db1 eval "INSERT INTO MOC($Variable_Column_Name) VALUES
>($Variable_Column_Value)"
>MTC { db1 eval "INSERT INTO MTC($Variable_Column_Name) VALUES
>($Variable_Column_Value)"
>FORW { db1 eval "INSERT INTO FORW($Variable_Column_Name) VALUES
>($Variable_Column_Value)"
>
>and so on... }
>
>where $Variable_Column_Name and $Variable_Column_Values are list
--
( Kees Nuyt
)
c[_]
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
