sql parameters performance

sql parameters performance

Postby Hengyi » Mon 19 Mar 2007 21:37

I wrote a Insert statement that uses parameters. There seems to be 3 ways to specify the parameters:

Method 1:
INSERT INTO table (a, b) VALUES (?, ?), (?, ?)

Method 2:
INSERT INTO table (a, b) VALUES (:p1, :p2), (:p3, :p4)

Method 3:
INSERT INTO table (a, b) VALUES (@p1, @p2), (@p3, @p4)

Which is the right (or recommended) way to do it? Also if the sql command has hundreds of parameters, I noticed that (1) is the fastest while (3) is the slowest. Why is that?

Thanks,
Hengyi
Hengyi
 
Posts: 3
Joined: Mon 19 Mar 2007 21:24

Postby Alexey » Tue 20 Mar 2007 07:15

There is no difference in performance, actually. You can use the way which suits your needs the best.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby Hengyi » Tue 20 Mar 2007 17:47

However, I did a test using a big Insert statement with 6000 parameters (The table has 26 columns and 230 rows are inserted in one statement). The performance diffs a lot.

Method 1: 30 ms
Method 2: 600 ms
Method 3: 1200 ms

Thanks,
Hengyi
Hengyi
 
Posts: 3
Joined: Mon 19 Mar 2007 21:24

Postby Alexey » Wed 21 Mar 2007 10:12

We will check this carefully.
Please send me your test project including the definition of your own database objects and data dump.
Use e-mail address provided in the Readme file.
Do not use third party components.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby Hengyi » Wed 21 Mar 2007 18:49

I've sent you the test program. Thanks.
Hengyi
 
Posts: 3
Joined: Mon 19 Mar 2007 21:24

Postby Alexey » Thu 22 Mar 2007 07:28

Your program is being investigated.
Look forward to hearing from me again.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43


Return to dotConnect for MySQL