Respect for your fellow human being. It doesn’t matter if you *think* she or he wants your attention, unless that person consents, keep your paws off!
Cory Sane back in March 2013 on SCN wrote about using unix domain sockets for bulk copying of data in/out of SAP Sybase ASE 15.7.
If you don’t know what unix domain sockets are, Thomas Stover over at Tech Deviancy wrote up an excellent Demystifying Unix Domain Sockets post. Highly recommended!
As usual, Wikipedia also has an article.
What is so great about unix domain sockets? You bypass the networking layer completely. This translates into faster communication between bcp (or similar) and ASE. The downside? The bcp file(s) must be on the same host as the ASE instance and your user must have read/write access to the ‘file’.
Using unix domain sockets with a 16k packet size appears to be ideal for the bcp out (1 million rows in the test). Notice the difference of time between using normal (tcp) connection of 5K rows / second compared to 104k rows / second connecting with unix domain socket.
tcp - packet size 65024 test #1 : Clock Time (ms.): total = 184821 Avg = 0 (5410.64 rows per sec.) test #2 : Clock Time (ms.): total = 178612 Avg = 0 (5598.73 rows per sec.) uds - packet size 2048 test #3 : Clock Time (ms.): total = 10843 Avg = 0 (92225.40 rows per sec.) test #4 : Clock Time (ms.): total = 11012 Avg = 0 (90810.03 rows per sec.) uds - packet size 8192 test #5 : Clock Time (ms.): total = 9823 Avg = 0 (101801.89 rows per sec.) test #6 : Clock Time (ms.): total = 9965 Avg = 0 (100351.23 rows per sec.) uds - packet size 12288 test #7 : Clock Time (ms.): total = 9735 Avg = 0 (102722.14 rows per sec.) test #8 : Clock Time (ms.): total = 9745 Avg = 0 (102616.73 rows per sec.) uds - packet size 16384 test #9 : Clock Time (ms.): total = 9587 Avg = 0 (104307.92 rows per sec.) test #10 : Clock Time (ms.): total = 9558 Avg = 0 (104624.40 rows per sec.) uds - packet size 32768 test #11 : Clock Time (ms.): total = 13205 Avg = 0 (75728.89 rows per sec.) test #12 : Clock Time (ms.): total = 12961 Avg = 0 (77154.54 rows per sec.) uds - packet size 65024 test #13 : Clock Time (ms.): total = 13254 Avg = 0 (75448.92 rows per sec.) test #14 : Clock Time (ms.): total = 13179 Avg = 0 (75878.29 rows per sec.)
With unix domain sockets, we come close to DTU speeds without the penalty of exclusive table locks and a DTU process you can’t kill. It really is like pushing the turbo button!
Obviously, if you are bulk copying out of a view that uses a monster index you you may not see a huge difference, if any, because the majority of the time will transverse that index.
You will see a larger boost with bulk copying out of data than in of data. Why? The network layer is usually not the bottleneck when inserting of data. The bottleneck tends to be more allocating/populating pages in the database or index population if you haven’t dropped the indexes. YMMV
Setting up unix domain sockets is very simple and does NOT require a reboot. Let’s me show you how:
The format of the query master lines you would add to the interfaces file is simple:
query afunix unused //hostname/directory_you_can_write_to/file_name master afunix unused //hostname/directory_you_can_write_to/file_name
query afunix unused //myase/dbms/sybase/ASE.socket master afunix unused //myase/dbms/sybase/ASE.socket
If you want to start the listener without restarting, use sp_listener:
sp_listener 'start', 'afunix://hostname:/directory_you_can_write_to/file_name'
sp_listener 'start', 'afunix://myase:/dbms/sybase/ASE.socket'
There are times when you need individual users the ability to determine what indexes are suggested by the index advisor but you don’t want or are unable to give them full dba access. All you need to do is grant the execute permission on the sp_iqindexadvice stored procedure.
Grant execute on sp_iqindexadvice to user; create table test (col1 int, col2 varchar(10)); insert into test values (1, 'test1'); insert into test values (2, 'test2'); insert into test values (3, 'test3'); insert into test values (4, 'test4'); commit; SET OPTION index_advisor = 'ON'; SET OPTION index_advisor_max_rows = 100; commit; select * from test where col1 =1 ; commit; call sp_iqindexadvice ();