Re: Witango-Talk: autocomplete speed comparison
If it was doing results caching, it would prob be quicker than 34ms. A simple small query like that should never be over 100ms. .NET is not going to cache results by default. The DB may cache queries by default, but that would help either app server. The bottom line is that .NET and PHP have take HUGE leaps forward when it comes to native DB connectivity and optimization. This is one of the areas where I have spent most of my time profiling and optimizing and researching over the years. Witango is just way behind. I would venture further to say v6 isn't going to help. v6 will use JDBC which will have trouble competing with .NET native connections and PHP mysqli. (not php mysql, but mysqli) -- Robert Garcia President - BigHead Technology VP Application Development - eventpix.com 13653 West Park Dr Magalia, Ca 95954 ph: 530.645.4040 x222 fax: 530.645.4040 rgar...@bighead.net - rgar...@eventpix.com http://bighead.net/ - http://eventpix.com/ On Oct 6, 2009, at 8:33 AM, Anthony Humphreys wrote: For one search action, anything less 100ms and I generally leave it be. But at 500ms or more I'm pulling (what little is left of) my hair out trying to fix the issue, as it is a real drain on performance, and has a negative impact on the user's (and client's) perception of the application I've built. Very long lists, very large results sets, and aggregated reports may take more than 500ms. Everything else, I try to get below that 100ms threshold. I generally find that when the SQL/DBMS are optimised, the rest just falls into place. If the same SQL is running against the same DBMS but takes 500+ms in Witango and is almost free in .NET, then I would suspect that .NET is doing results caching. On Tue, Oct 6, 2009 at 10:54 AM, Ted Wolfley wrote: Witango speed stayed about the same on repeat queries and the datsourcelife is set at the default. The ajax taf is tiny and using the Direct_DBMS for the query via a tcf. The database connection is opened at login to the site. I guess we will go with the .NET version until we see what Witango 6 has to offer. Thanks Ted From: Robert Garcia [mailto:wita...@bighead.net] Sent: Tuesday, October 06, 2009 10:12 AM To: witango-talk@witango.com Subject: Re: Witango-Talk: autocomplete speed comparison ODBC and persistent connections would be my guess, as long as ALL OTHER variables are equal. Repeat the query a few times, and see if it is faster. Witango is most likely using ODBC connector and .NET is using a more native, optimized connection. Witango will always take longer on its FIRST query to a connection, due to the overhead of opening a connection from scratch. Witango will then hold that connection in memory and try to reuse it, saving time on subsequent queries. This seems efficient, but it actually ends up wasting memory on both the witango side and especially the db server side. Check the documentation on the datasourcelife configuration variable. -- Robert Garcia President - BigHead Technology VP Application Development - eventpix.com 13653 West Park Dr Magalia, Ca 95954 ph: 530.645.4040 x222 fax: 530.645.4040 rgar...@bighead.net - rgar...@eventpix.com http://bighead.net/ - http://eventpix.com/ On Oct 6, 2009, at 6:44 AM, Ted Wolfley wrote: Hi, We created a simple ajax autocomplete retrieving name and company from a sql server 2005 table on another server. We compared 2 different options. Both servers are Windows 2003 32-bit Using Witango 5.5, the query request took 528ms. Using ASP.Net, the query request took 34 ms The sql statement is the same for both queries Wondering if someone could give an explanation why there is so much difference in speed and is there a way to speed up the Witango query. Ted Wolfley Lead Internet and Database Programmer The Ogden Group of Rochester phone: (585) 321 1060 x23 fax: (585) 321 0043 t...@ogdengroup.com www.ogdengroup.com TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
Re: Witango-Talk: autocomplete speed comparison
For one search action, anything less 100ms and I generally leave it be. But at 500ms or more I'm pulling (what little is left of) my hair out trying to fix the issue, as it is a real drain on performance, and has a negative impact on the user's (and client's) perception of the application I've built. Very long lists, very large results sets, and aggregated reports may take more than 500ms. Everything else, I try to get below that 100ms threshold. I generally find that when the SQL/DBMS are optimised, the rest just falls into place. If the same SQL is running against the same DBMS but takes 500+ms in Witango and is almost free in .NET, then I would suspect that .NET is doing results caching. On Tue, Oct 6, 2009 at 10:54 AM, Ted Wolfley wrote: > Witango speed stayed about the same on repeat queries and the > datsourcelife is set at the default. > > The ajax taf is tiny and using the Direct_DBMS for the query via a tcf. > The database connection is opened at login to the site. > > > > I guess we will go with the .NET version until we see what Witango 6 has to > offer. > > > > Thanks > > > > Ted > > > -- > > *From:* Robert Garcia [mailto:wita...@bighead.net] > *Sent:* Tuesday, October 06, 2009 10:12 AM > > *To:* witango-talk@witango.com > *Subject:* Re: Witango-Talk: autocomplete speed comparison > > > > ODBC and persistent connections would be my guess, as long as ALL OTHER > variables are equal. Repeat the query a few times, and see if it is faster. > Witango is most likely using ODBC connector and .NET is using a more native, > optimized connection. Witango will always take longer on its FIRST query to > a connection, due to the overhead of opening a connection from scratch. > Witango will then hold that connection in memory and try to reuse it, saving > time on subsequent queries. This seems efficient, but it actually ends up > wasting memory on both the witango side and especially the db server side. > > > > Check the documentation on the datasourcelife configuration variable. > > > > -- > > > > Robert Garcia > > President - BigHead Technology > > VP Application Development - eventpix.com > > 13653 West Park Dr > > Magalia, Ca 95954 > > ph: 530.645.4040 x222 fax: 530.645.4040 > > rgar...@bighead.net - rgar...@eventpix.com > > http://bighead.net/ - http://eventpix.com/ > > > > On Oct 6, 2009, at 6:44 AM, Ted Wolfley wrote: > > > > Hi, > > > > We created a simple ajax autocomplete retrieving name and company from a > sql server 2005 table on another server. We compared 2 different options. > > > > Both servers are Windows 2003 32-bit > > > > Using Witango 5.5, the query request took 528ms. > > Using ASP.Net, the query request took 34 ms > > > > The sql statement is the same for both queries > > > > Wondering if someone could give an explanation why there is so much > difference in speed and is there a way to speed up the Witango query. > > > > Ted Wolfley > Lead Internet and Database Programmer > The Ogden Group of Rochester > phone: (585) 321 1060 x23 > fax: (585) 321 0043 > t...@ogdengroup.com > > www.ogdengroup.com > > > > > > TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf > > > > > TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf > > > TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf > > TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
RE: Witango-Talk: autocomplete speed comparison
Tested both odbc, with the same result. Thanks for all the help, not going to dwell on it any longer. The autocompleter needs to keep up with the typist and that means go with the faster code. Ted From: Robert Garcia [mailto:wita...@bighead.net] Sent: Tuesday, October 06, 2009 11:10 AM To: witango-talk@witango.com Subject: Re: Witango-Talk: autocomplete speed comparison Witango is probably always going to be slower, but 528ms seems excessive. TCF vs TAF should only add a couple of milliseconds. The other thing I would check is how you specify the datasource in the ODBC connection setup for witango. Try using an ip address if you are using a hostname. I have seen where dns is slow and can cause this. It could also be that you are using a version of the SQL 2005 ODBC driver that witango isn't too happy with, I have also seen where changing versions of ODBC can have a significant impact. It is difficult, because witango is so out of date that you have to setup a machine with older versions of software that witango is comfortable with. We have 2 small witango servers still serving a few old apps and we have to use OLD java version 1.4, older mysql ODBC drivers, etc etc. If you can't find a quick solution to the huge difference, I would stick with .net and save the headaches. -- Robert Garcia President - BigHead Technology VP Application Development - eventpix.com 13653 West Park Dr Magalia, Ca 95954 ph: 530.645.4040 x222 fax: 530.645.4040 rgar...@bighead.net - rgar...@eventpix.com http://bighead.net/ - http://eventpix.com/ On Oct 6, 2009, at 7:54 AM, Ted Wolfley wrote: Witango speed stayed about the same on repeat queries and the datsourcelife is set at the default. The ajax taf is tiny and using the Direct_DBMS for the query via a tcf. The database connection is opened at login to the site. I guess we will go with the .NET version until we see what Witango 6 has to offer. Thanks Ted From: Robert Garcia [mailto:wita...@bighead.net] Sent: Tuesday, October 06, 2009 10:12 AM To: witango-talk@witango.com Subject: Re: Witango-Talk: autocomplete speed comparison ODBC and persistent connections would be my guess, as long as ALL OTHER variables are equal. Repeat the query a few times, and see if it is faster. Witango is most likely using ODBC connector and .NET is using a more native, optimized connection. Witango will always take longer on its FIRST query to a connection, due to the overhead of opening a connection from scratch. Witango will then hold that connection in memory and try to reuse it, saving time on subsequent queries. This seems efficient, but it actually ends up wasting memory on both the witango side and especially the db server side. Check the documentation on the datasourcelife configuration variable. -- Robert Garcia President - BigHead Technology VP Application Development - eventpix.com <http://eventpix.com/> 13653 West Park Dr Magalia, Ca 95954 ph: 530.645.4040 x222 fax: 530.645.4040 rgar...@bighead.net - rgar...@eventpix.com http://bighead.net/ - http://eventpix.com/ On Oct 6, 2009, at 6:44 AM, Ted Wolfley wrote: Hi, We created a simple ajax autocomplete retrieving name and company from a sql server 2005 table on another server. We compared 2 different options. Both servers are Windows 2003 32-bit Using Witango 5.5, the query request took 528ms. Using ASP.Net <http://ASP.Net/> , the query request took 34 ms The sql statement is the same for both queries Wondering if someone could give an explanation why there is so much difference in speed and is there a way to speed up the Witango query. Ted Wolfley Lead Internet and Database Programmer The Ogden Group of Rochester phone: (585) 321 1060 x23 fax: (585) 321 0043 t...@ogdengroup.com <mailto:t...@ogdengroup.com> www.ogdengroup.com <http://www.ogdengroup.com/> TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
Re: Witango-Talk: autocomplete speed comparison
Witango is probably always going to be slower, but 528ms seems excessive. TCF vs TAF should only add a couple of milliseconds. The other thing I would check is how you specify the datasource in the ODBC connection setup for witango. Try using an ip address if you are using a hostname. I have seen where dns is slow and can cause this. It could also be that you are using a version of the SQL 2005 ODBC driver that witango isn't too happy with, I have also seen where changing versions of ODBC can have a significant impact. It is difficult, because witango is so out of date that you have to setup a machine with older versions of software that witango is comfortable with. We have 2 small witango servers still serving a few old apps and we have to use OLD java version 1.4, older mysql ODBC drivers, etc etc. If you can't find a quick solution to the huge difference, I would stick with .net and save the headaches. -- Robert Garcia President - BigHead Technology VP Application Development - eventpix.com 13653 West Park Dr Magalia, Ca 95954 ph: 530.645.4040 x222 fax: 530.645.4040 rgar...@bighead.net - rgar...@eventpix.com http://bighead.net/ - http://eventpix.com/ On Oct 6, 2009, at 7:54 AM, Ted Wolfley wrote: Witango speed stayed about the same on repeat queries and the datsourcelife is set at the default. The ajax taf is tiny and using the Direct_DBMS for the query via a tcf. The database connection is opened at login to the site. I guess we will go with the .NET version until we see what Witango 6 has to offer. Thanks Ted From: Robert Garcia [mailto:wita...@bighead.net] Sent: Tuesday, October 06, 2009 10:12 AM To: witango-talk@witango.com Subject: Re: Witango-Talk: autocomplete speed comparison ODBC and persistent connections would be my guess, as long as ALL OTHER variables are equal. Repeat the query a few times, and see if it is faster. Witango is most likely using ODBC connector and .NET is using a more native, optimized connection. Witango will always take longer on its FIRST query to a connection, due to the overhead of opening a connection from scratch. Witango will then hold that connection in memory and try to reuse it, saving time on subsequent queries. This seems efficient, but it actually ends up wasting memory on both the witango side and especially the db server side. Check the documentation on the datasourcelife configuration variable. -- Robert Garcia President - BigHead Technology VP Application Development - eventpix.com 13653 West Park Dr Magalia, Ca 95954 ph: 530.645.4040 x222 fax: 530.645.4040 rgar...@bighead.net - rgar...@eventpix.com http://bighead.net/ - http://eventpix.com/ On Oct 6, 2009, at 6:44 AM, Ted Wolfley wrote: Hi, We created a simple ajax autocomplete retrieving name and company from a sql server 2005 table on another server. We compared 2 different options. Both servers are Windows 2003 32-bit Using Witango 5.5, the query request took 528ms. Using ASP.Net, the query request took 34 ms The sql statement is the same for both queries Wondering if someone could give an explanation why there is so much difference in speed and is there a way to speed up the Witango query. Ted Wolfley Lead Internet and Database Programmer The Ogden Group of Rochester phone: (585) 321 1060 x23 fax: (585) 321 0043 t...@ogdengroup.com www.ogdengroup.com TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
Re: Witango-Talk: autocomplete speed comparison
On Tue, Oct 6, 2009 at 9:44 AM, Ted Wolfley wrote: > Using Witango 5.5, the query request took 528ms. > > Using ASP.Net, the query request took 34 ms > > Ted, Have you tested both ODBC drivers with Witango, the "SQL Native Client" driver and the "SQL Server" driver? Bill TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
RE: Witango-Talk: autocomplete speed comparison
There is no other code in the ajax_taf at all? I have written similar applications in Witango, Ajax updating a choicelist based on user input selected from my database (also SQL 2005) and I do see execution times below 50 ms. The web page updates nearly instantly. I suspect that there is optimization that can be done in your TAF. I don't, however, have experience with .NET so I can't be sure that Witango is always going to be slower than it or not. Robert _ From: Ted Wolfley [mailto:t...@ogdengroup.com] Sent: Tuesday, October 06, 2009 10:54 AM To: witango-talk@witango.com Subject: RE: Witango-Talk: autocomplete speed comparison Witango speed stayed about the same on repeat queries and the datsourcelife is set at the default. The ajax taf is tiny and using the Direct_DBMS for the query via a tcf. The database connection is opened at login to the site. I guess we will go with the .NET version until we see what Witango 6 has to offer. Thanks Ted _ From: Robert Garcia [mailto:wita...@bighead.net] Sent: Tuesday, October 06, 2009 10:12 AM To: witango-talk@witango.com Subject: Re: Witango-Talk: autocomplete speed comparison ODBC and persistent connections would be my guess, as long as ALL OTHER variables are equal. Repeat the query a few times, and see if it is faster. Witango is most likely using ODBC connector and .NET is using a more native, optimized connection. Witango will always take longer on its FIRST query to a connection, due to the overhead of opening a connection from scratch. Witango will then hold that connection in memory and try to reuse it, saving time on subsequent queries. This seems efficient, but it actually ends up wasting memory on both the witango side and especially the db server side. Check the documentation on the datasourcelife configuration variable. -- Robert Garcia President - BigHead Technology VP Application Development - eventpix.com 13653 West Park Dr Magalia, Ca 95954 ph: 530.645.4040 x222 fax: 530.645.4040 rgar...@bighead.net - rgar...@eventpix.com http://bighead.net/ - http://eventpix.com/ On Oct 6, 2009, at 6:44 AM, Ted Wolfley wrote: Hi, We created a simple ajax autocomplete retrieving name and company from a sql server 2005 table on another server. We compared 2 different options. Both servers are Windows 2003 32-bit Using Witango 5.5, the query request took 528ms. Using ASP.Net, the query request took 34 ms The sql statement is the same for both queries Wondering if someone could give an explanation why there is so much difference in speed and is there a way to speed up the Witango query. Ted Wolfley Lead Internet and Database Programmer The Ogden Group of Rochester phone: (585) 321 1060 x23 fax: (585) 321 0043 <mailto:t...@ogdengroup.com> t...@ogdengroup.com <http://www.ogdengroup.com/> www.ogdengroup.com TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
Re: Witango-Talk: autocomplete speed comparison
Hi Ted, If you're already using a Direct_DBMS in a TCF, then swap out your TAF for a TML file, I think you may see some improvement in speed. TML files have a lot less overhead than TAF files do. Hope that helps. Scott, On Oct-6-2009, at 11:54 AM, Ted Wolfley wrote: Witango speed stayed about the same on repeat queries and the datsourcelife is set at the default. The ajax taf is tiny and using the Direct_DBMS for the query via a tcf. The database connection is opened at login to the site. I guess we will go with the .NET version until we see what Witango 6 has to offer. Thanks Ted From: Robert Garcia [mailto:wita...@bighead.net] Sent: Tuesday, October 06, 2009 10:12 AM To: witango-talk@witango.com Subject: Re: Witango-Talk: autocomplete speed comparison ODBC and persistent connections would be my guess, as long as ALL OTHER variables are equal. Repeat the query a few times, and see if it is faster. Witango is most likely using ODBC connector and .NET is using a more native, optimized connection. Witango will always take longer on its FIRST query to a connection, due to the overhead of opening a connection from scratch. Witango will then hold that connection in memory and try to reuse it, saving time on subsequent queries. This seems efficient, but it actually ends up wasting memory on both the witango side and especially the db server side. Check the documentation on the datasourcelife configuration variable. -- Robert Garcia President - BigHead Technology VP Application Development - eventpix.com 13653 West Park Dr Magalia, Ca 95954 ph: 530.645.4040 x222 fax: 530.645.4040 rgar...@bighead.net - rgar...@eventpix.com http://bighead.net/ - http://eventpix.com/ On Oct 6, 2009, at 6:44 AM, Ted Wolfley wrote: Hi, We created a simple ajax autocomplete retrieving name and company from a sql server 2005 table on another server. We compared 2 different options. Both servers are Windows 2003 32-bit Using Witango 5.5, the query request took 528ms. Using ASP.Net, the query request took 34 ms The sql statement is the same for both queries Wondering if someone could give an explanation why there is so much difference in speed and is there a way to speed up the Witango query. Ted Wolfley Lead Internet and Database Programmer The Ogden Group of Rochester phone: (585) 321 1060 x23 fax: (585) 321 0043 t...@ogdengroup.com www.ogdengroup.com TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
RE: Witango-Talk: autocomplete speed comparison
Witango speed stayed about the same on repeat queries and the datsourcelife is set at the default. The ajax taf is tiny and using the Direct_DBMS for the query via a tcf. The database connection is opened at login to the site. I guess we will go with the .NET version until we see what Witango 6 has to offer. Thanks Ted From: Robert Garcia [mailto:wita...@bighead.net] Sent: Tuesday, October 06, 2009 10:12 AM To: witango-talk@witango.com Subject: Re: Witango-Talk: autocomplete speed comparison ODBC and persistent connections would be my guess, as long as ALL OTHER variables are equal. Repeat the query a few times, and see if it is faster. Witango is most likely using ODBC connector and .NET is using a more native, optimized connection. Witango will always take longer on its FIRST query to a connection, due to the overhead of opening a connection from scratch. Witango will then hold that connection in memory and try to reuse it, saving time on subsequent queries. This seems efficient, but it actually ends up wasting memory on both the witango side and especially the db server side. Check the documentation on the datasourcelife configuration variable. -- Robert Garcia President - BigHead Technology VP Application Development - eventpix.com 13653 West Park Dr Magalia, Ca 95954 ph: 530.645.4040 x222 fax: 530.645.4040 rgar...@bighead.net - rgar...@eventpix.com http://bighead.net/ - http://eventpix.com/ On Oct 6, 2009, at 6:44 AM, Ted Wolfley wrote: Hi, We created a simple ajax autocomplete retrieving name and company from a sql server 2005 table on another server. We compared 2 different options. Both servers are Windows 2003 32-bit Using Witango 5.5, the query request took 528ms. Using ASP.Net, the query request took 34 ms The sql statement is the same for both queries Wondering if someone could give an explanation why there is so much difference in speed and is there a way to speed up the Witango query. Ted Wolfley Lead Internet and Database Programmer The Ogden Group of Rochester phone: (585) 321 1060 x23 fax: (585) 321 0043 t...@ogdengroup.com <mailto:t...@ogdengroup.com> www.ogdengroup.com <http://www.ogdengroup.com/> TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
Re: Witango-Talk: autocomplete speed comparison
Also you can try open a connection to the DB sometime in the start of the users interaction with you site. could be something in the background and a very simple small query but that will assure a connection is setup and available for reuse. I don't think you are going to beat the ,et direct connection but you should be able to get closer. -- Dan Stein MSN Digital Software Solutions d...@dss-db.com www.dss-db.com On Oct 6, 2009, at 10:17 AM, Anthony Humphreys wrote: If you were to turn on Wiango's Debug, the number as the far left show the time taken. Where is the longest delay, and work to reduce that. If you don't have caching turned on, the act of having to load the taf file will add a delay and increase the time. Remove unnecessary actions before the one which returns your results. Large TAF files with lots of IF/ELSEIF actions will become slower for the latter actions. Witango puts the DB connection into a "pool" for re-use. The first use of a database connection is slower than subsequent uses of the same datasource. How long Witango keeps datasource connections open can be controlled. Witango does cache the results of commonly used queries, every query is done fresh from the DBMS. Witango has been accused of being slow when using its looping featues, @FOR and @ROWS, especially on on large result sets (arrays). Is there any way to re-write the output to avoid using the @ROWS, ie using the array parameters of @VAR, ie APREFIX, ASUFFIX, RPREFIX, RSUFFIX, CPREFIX, CSUFFIX see http://humphreys.org/reference/Witango4.5_Help/meta/metata7a.htm On 10/6/09, Ted Wolfley wrote: Hi, We created a simple ajax autocomplete retrieving name and company from a sql server 2005 table on another server. We compared 2 different options. Both servers are Windows 2003 32-bit Using Witango 5.5, the query request took 528ms. Using ASP.Net, the query request took 34 ms The sql statement is the same for both queries Wondering if someone could give an explanation why there is so much difference in speed and is there a way to speed up the Witango query. Ted Wolfley Lead Internet and Database Programmer The Ogden Group of Rochester phone: (585) 321 1060 x23 fax: (585) 321 0043 t...@ogdengroup.com www.ogdengroup.com TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
RE: Witango-Talk: autocomplete speed comparison
Sql server 2005 32-bit standard edition From: Dan Stein [mailto:d...@dss-db.com] Sent: Tuesday, October 06, 2009 10:06 AM To: witango-talk@witango.com Subject: Re: Witango-Talk: autocomplete speed comparison what version of SQL server -- Dan Stein MSN FileMaker 7 Certified Developer FileMaker 9 Certified Developer Digital Software Solutions 303 W. Chestnut St Souderton PA 18964 Land: 215-799-0192 Cell: 610-256-2843 Fax 215-799-0192 ( Call 1st) FMP, WiTango,MSSQL, MySQL,PHP d...@dss-db.com www.dss-db.com "There is a larger frame to the painting than the one that bounds our life's events." On Oct 6, 2009, at 9:44 AM, Ted Wolfley wrote: Hi, We created a simple ajax autocomplete retrieving name and company from a sql server 2005 table on another server. We compared 2 different options. Both servers are Windows 2003 32-bit Using Witango 5.5, the query request took 528ms. Using ASP.Net, the query request took 34 ms The sql statement is the same for both queries Wondering if someone could give an explanation why there is so much difference in speed and is there a way to speed up the Witango query. Ted Wolfley Lead Internet and Database Programmer The Ogden Group of Rochester phone: (585) 321 1060 x23 fax: (585) 321 0043 t...@ogdengroup.com <mailto:t...@ogdengroup.com> www.ogdengroup.com <http://www.ogdengroup.com/> TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
Re: Witango-Talk: autocomplete speed comparison
Should have read: Witango does NOT cache the results of commonly used queries, every query is done fresh from the DBMS. On 10/6/09, Anthony Humphreys wrote: > > If you were to turn on Wiango's Debug, the number as the far left show the > time taken. Where is the longest delay, and work to reduce that. > > If you don't have caching turned on, the act of having to load the taf file > will add a delay and increase the time. > > Remove unnecessary actions before the one which returns your results. Large > TAF files with lots of IF/ELSEIF actions will become slower for the latter > actions. > > Witango puts the DB connection into a "pool" for re-use. The first use of a > database connection is slower than subsequent uses of the same datasource. > How long Witango keeps datasource connections open can be controlled. > Witango does cache the results of commonly used queries, every query is done > fresh from the DBMS. > > Witango has been accused of being slow when using its looping featues, @FOR > and @ROWS, especially on on large result sets (arrays). Is there any way to > re-write the output to avoid using the @ROWS, ie using the array parameters > of @VAR, ie APREFIX, ASUFFIX, RPREFIX, RSUFFIX, CPREFIX, CSUFFIX > see http://humphreys.org/reference/Witango4.5_Help/meta/metata7a.htm > > > > > On 10/6/09, Ted Wolfley wrote: >> >> Hi, >> >> >> >> We created a simple ajax autocomplete retrieving name and company from a >> sql server 2005 table on another server. We compared 2 different options. >> >> >> >> Both servers are Windows 2003 32-bit >> >> >> >> Using Witango 5.5, the query request took 528ms. >> >> Using ASP.Net, the query request took 34 ms >> >> >> >> The sql statement is the same for both queries >> >> >> >> Wondering if someone could give an explanation why there is so much >> difference in speed and is there a way to speed up the Witango query. >> >> >> >> Ted Wolfley >> Lead Internet and Database Programmer >> The Ogden Group of Rochester >> phone: (585) 321 1060 x23 >> fax: (585) 321 0043 >> t...@ogdengroup.com >> >> www.ogdengroup.com >> >> >> >> >> TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf >> >> > TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
Re: Witango-Talk: autocomplete speed comparison
what version of SQL server -- Dan Stein MSN FileMaker 7 Certified Developer FileMaker 9 Certified Developer Digital Software Solutions 303 W. Chestnut St Souderton PA 18964 Land: 215-799-0192 Cell: 610-256-2843 Fax 215-799-0192 ( Call 1st) FMP, WiTango,MSSQL, MySQL,PHP d...@dss-db.com www.dss-db.com "There is a larger frame to the painting than the one that bounds our life's events." On Oct 6, 2009, at 9:44 AM, Ted Wolfley wrote: Hi, We created a simple ajax autocomplete retrieving name and company from a sql server 2005 table on another server. We compared 2 different options. Both servers are Windows 2003 32-bit Using Witango 5.5, the query request took 528ms. Using ASP.Net, the query request took 34 ms The sql statement is the same for both queries Wondering if someone could give an explanation why there is so much difference in speed and is there a way to speed up the Witango query. Ted Wolfley Lead Internet and Database Programmer The Ogden Group of Rochester phone: (585) 321 1060 x23 fax: (585) 321 0043 t...@ogdengroup.com www.ogdengroup.com TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
Re: Witango-Talk: autocomplete speed comparison
If you were to turn on Wiango's Debug, the number as the far left show the time taken. Where is the longest delay, and work to reduce that. If you don't have caching turned on, the act of having to load the taf file will add a delay and increase the time. Remove unnecessary actions before the one which returns your results. Large TAF files with lots of IF/ELSEIF actions will become slower for the latter actions. Witango puts the DB connection into a "pool" for re-use. The first use of a database connection is slower than subsequent uses of the same datasource. How long Witango keeps datasource connections open can be controlled. Witango does cache the results of commonly used queries, every query is done fresh from the DBMS. Witango has been accused of being slow when using its looping featues, @FOR and @ROWS, especially on on large result sets (arrays). Is there any way to re-write the output to avoid using the @ROWS, ie using the array parameters of @VAR, ie APREFIX, ASUFFIX, RPREFIX, RSUFFIX, CPREFIX, CSUFFIX see http://humphreys.org/reference/Witango4.5_Help/meta/metata7a.htm On 10/6/09, Ted Wolfley wrote: > > Hi, > > > > We created a simple ajax autocomplete retrieving name and company from a > sql server 2005 table on another server. We compared 2 different options. > > > > Both servers are Windows 2003 32-bit > > > > Using Witango 5.5, the query request took 528ms. > > Using ASP.Net, the query request took 34 ms > > > > The sql statement is the same for both queries > > > > Wondering if someone could give an explanation why there is so much > difference in speed and is there a way to speed up the Witango query. > > > > Ted Wolfley > Lead Internet and Database Programmer > The Ogden Group of Rochester > phone: (585) 321 1060 x23 > fax: (585) 321 0043 > t...@ogdengroup.com > > www.ogdengroup.com > > > > > TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf > > TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
Re: Witango-Talk: autocomplete speed comparison
ODBC and persistent connections would be my guess, as long as ALL OTHER variables are equal. Repeat the query a few times, and see if it is faster. Witango is most likely using ODBC connector and .NET is using a more native, optimized connection. Witango will always take longer on its FIRST query to a connection, due to the overhead of opening a connection from scratch. Witango will then hold that connection in memory and try to reuse it, saving time on subsequent queries. This seems efficient, but it actually ends up wasting memory on both the witango side and especially the db server side. Check the documentation on the datasourcelife configuration variable. -- Robert Garcia President - BigHead Technology VP Application Development - eventpix.com 13653 West Park Dr Magalia, Ca 95954 ph: 530.645.4040 x222 fax: 530.645.4040 rgar...@bighead.net - rgar...@eventpix.com http://bighead.net/ - http://eventpix.com/ On Oct 6, 2009, at 6:44 AM, Ted Wolfley wrote: Hi, We created a simple ajax autocomplete retrieving name and company from a sql server 2005 table on another server. We compared 2 different options. Both servers are Windows 2003 32-bit Using Witango 5.5, the query request took 528ms. Using ASP.Net, the query request took 34 ms The sql statement is the same for both queries Wondering if someone could give an explanation why there is so much difference in speed and is there a way to speed up the Witango query. Ted Wolfley Lead Internet and Database Programmer The Ogden Group of Rochester phone: (585) 321 1060 x23 fax: (585) 321 0043 t...@ogdengroup.com www.ogdengroup.com TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
Re: Witango-Talk: autocomplete speed comparison
woops sorry I see that 2005 my guess would be 2005 is optimized for .net and the ajax where witango is so long in the tooth now it is not. One would expect 6 to improve on this. You might try contact witango support about this and about the expectation for 6 it is the kind of question they should answer. I would CC sales also. Let us know what you hear back or if you don't -- Dan Stein MSN FileMaker 7 Certified Developer FileMaker 9 Certified Developer Digital Software Solutions 303 W. Chestnut St Souderton PA 18964 Land: 215-799-0192 Cell: 610-256-2843 Fax 215-799-0192 ( Call 1st) FMP, WiTango,MSSQL, MySQL,PHP d...@dss-db.com www.dss-db.com "When you are born, you cry and those who love you rejoice. And if you live your life as you should, when you die, you rejoice and those who love you cry." On Oct 6, 2009, at 9:44 AM, Ted Wolfley wrote: Hi, We created a simple ajax autocomplete retrieving name and company from a sql server 2005 table on another server. We compared 2 different options. Both servers are Windows 2003 32-bit Using Witango 5.5, the query request took 528ms. Using ASP.Net, the query request took 34 ms The sql statement is the same for both queries Wondering if someone could give an explanation why there is so much difference in speed and is there a way to speed up the Witango query. Ted Wolfley Lead Internet and Database Programmer The Ogden Group of Rochester phone: (585) 321 1060 x23 fax: (585) 321 0043 t...@ogdengroup.com www.ogdengroup.com TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf