5

it's my first time here just to ask some questions I couldn't seem to find the answer to. Basically, I created a system that would accept fingerprint of a person and will login him/her into the system. But as time goes on, I have thousands of record and the fingerprint searching has become much slower than it used to.

The basic process of what I do is that I select all the biometrics data from a table and in my code, I use a foreach loop to go through one by one as the fingerprint template compare it to teach until it gets the right one.

Can you give some tips to make my selection much faster? Any help would be appreciated.

using (conn = new SqlConnection(connString)) {
    conn.Open();
     using (comm = new SqlCommand("EXEC dbn.sp_opd_selectBiometrics", conn)) {
        using (adap = new SqlDataAdapter(comm)) {
           using (dt = new DataTable()) {
                adap.Fill(dt);
                  if (dt.Rows.Count > 0) {
                    foreach (DataRow dr in dt.Rows) {
                     //Deserialize fingerprint template to bytes for verification
                       try {
                          byte[] _img = (byte[])dr["biometrics"];
                          string byteBiometrics = Encoding.UTF8.GetString(_img, 0, _img.Length);
                          getHcode = (string)dr["hpercode"];
                          MemoryStream ms = new MemoryStream(_img);

                           Template = new DPFP.Template();
                           Template.DeSerialize(ms);
                           Verificator = new DPFP.Verification.Verification();
// more code here...
       }
      }
    }
  }
}
gotqn
  • 42,737
  • 46
  • 157
  • 243
Danny Boy
  • 51
  • 2
  • Is the fingerprint data saved in SQL server as byte array? – Hasan Emrah Süngü Aug 07 '18 at 03:43
  • Yes sir, it's saved as varbinary(MAX). – Danny Boy Aug 07 '18 at 03:44
  • Yeah, assuming you are doing everything correctly, and storing the right information, the only think i can think of is maybe a crl stored procedure and get this happening on the server side, if thats possible – TheGeneral Aug 07 '18 at 03:45
  • Would it be a bad idea to create index on SQL server for each finger print data? Then you can query the database for the exact match – Hasan Emrah Süngü Aug 07 '18 at 03:45
  • IMO, without looking at sp_opd_selectBiometrics ,it is hard to suggest about index or CLR SP.you should show your proc if it slow and how many record proc will return.Above all once you should try using Sql data Reader instead of datatable. – KumarHarsh Aug 07 '18 at 03:49
  • @KumarHarsh, it is highly `unlikely` that [retrieving all values from DB to memory then scanning each and every row for a finger print match] can be more performant than a simple query which uses index. – Hasan Emrah Süngü Aug 07 '18 at 03:53
  • My sp_opd_selectBiometrics only has one query, actually, as I'm testing it out. select * from [mytable] where biometrics is not null – Danny Boy Aug 07 '18 at 03:55
  • First, get each of 1000 records 10 first bytes then compare to the user input bytes. Then next step we will have much less data to compare and it will be faster. – Red Wei Aug 07 '18 at 04:14
  • Yes Applying some more filter in proc will be faster.Why don't you pass direct finger print or like what @RedWei is saying.You may keep some otherid to identify user whch may not be unique.for example ReginID,cityID or GroupID ,EmailID or DOB which will further filter out records. – KumarHarsh Aug 07 '18 at 04:24
  • My guess is the the procedure is very complex. I'm guessing that the fingerprint byte array may not be consistent anywhere. Think of a reference fingerprint and one taken off that knife you were holding - the smart algorithm can match them, but the first 10 bytes or any other measure will not. That being said, is there some mini-low-res version that can be used as a fast indexer? If not then you might be out of luck as its probably the smarts that actually takes the time. – TomC Aug 07 '18 at 04:40
  • Thanks guys, I decided to try out Red Wei's version and I saw some improvement with my selection time. Meanwhile, I'll tinker more to see if there's anything I can improve. – Danny Boy Aug 07 '18 at 04:48
  • I think you can use parallel programming. – juliushuck Aug 07 '18 at 05:12
  • write sp_opd_selectBiometrics content. back-end site needs usually are more important. How long do you have to wait to get the result of the stored procedure? – Ali Elmi Aug 07 '18 at 05:30
  • 1
    Different biometric solutions have different performance characteristics. Without naming and shaming, I suspect from classnames and personal experience that practically all of your time is spent performing the verifications within the 3rd party SDK. Some other SDKs are an order of magnitude faster. My suggestion would be to measure (profile) where your time is actually being spent. If you are stuck with a slower SDK, you may need to look for ways to guess who is likely to be presenting and check them first, with an early exit assuming your FAR is small enough. – Adam G Aug 07 '18 at 13:05
  • Hey, thanks for your comments. I managed to make the waiting time shorter. Previously, it used to be 10-20 seconds, but now, I was able to make it more or less just 5 seconds by fixing my looping and removing unnecessary duplicate codes. – Danny Boy Aug 08 '18 at 03:40
  • Well there's no advantage at all to using a dataadapter and datatable, just use a data reader instead. – Andy Sep 29 '18 at 16:23

1 Answers1

2

The searching by text is usually slow and becomes slower if the text is large and the data is huge. I doubt you will get better solution in the context of the .net in compare pure T-SQL solution.

The steps are as follows:

  1. alter your table and add bio-metrics-hash column
  2. create non-clustered index on this column
  3. make a string with all your bio-metrics data, compute the hash of this value and populate the new column for current records (update the data)
  4. when new record is created/updated, automatically calculated the corresponding hash

You are ready to search.

In your application, call a stored procedure with input parameter bio-metrics-data. In the procedure, calculated the hash of this value and perform search by the hash value only.

Note, that depending on your bio-metrics data size, you may need to use different approach, but the idea is the same.

In SQL Server 2016 +, the HASHBYTES functions allows max values as argument.

gotqn
  • 42,737
  • 46
  • 157
  • 243
  • 1
    Thanks for your suggestion, but with every fingerprint registration, the value is not entirely the same as the saved one. There's still the process of Serialization and DeSerialization that generates a different hash each time. I was able to use your logic in another, though. Thanks! – Danny Boy Aug 07 '18 at 08:19
  • @DannyBoy how where u able to use this logic, please let me know – Favor Feb 21 '19 at 16:33