Gossamer Forum
Home : Products : Gossamer Links : Discussions :

MS SQL 2000 slow as hell (yes I know its MS!)

Quote Reply
MS SQL 2000 slow as hell (yes I know its MS!)
Evening!



I’ve been getting complaints from my boss about the slow speeds of LinksSQL (2.1.2) on a Windows 2000 server, connected to a MS SQL Server 2000. If you want to check out the site, go to http://www.thebigtravelguide.com/ and try the search or add record. You should notice a lag, and sometimes an error (something about fetchrow_array, if this happens hit refresh and all should be well).



Are there any other people having similar problems on a MS setup? Apparently all the versions of Perl, DBI and ODBC are up to date, in fact here’s what the host told us…



“I tried a few different queries whilst monitoring the CPU usage on the server. The server is definitely not under duress and your database is tiny compared to some of the others that we have. Further to this I have checked your usage stats and your site is not running many users at a time. I can see no reason why it would be running slow from the server side of things. It may have something to do with the way the database is structured.”



If anyone has some info, comments or general thoughts about this (I’ll even have ‘MS is Bad’ comments if they come up with a solution!)

Thanks!

Chris Williams (cwi@workslikeadream.com)
Quote Reply
Re: [Scampi] MS SQL 2000 slow as hell (yes I know its MS!) In reply to
Are you using internal indexing? This could speed things up a lot if you have no indexing at the moment.
Quote Reply
Re: [afinlr] MS SQL 2000 slow as hell (yes I know its MS!) In reply to
Thanks for the quick reply!

How would I check this? (Sorry, not very good with msSQL)
Scampi.

-----------------------------------------------------
A Little Madness Now And Then,
Is Relished By The Wisest Men
- Mr Willy Wonka
-----------------------------------------------------
Quote Reply
Re: [Scampi] MS SQL 2000 slow as hell (yes I know its MS!) In reply to
You find these options in the admin->database area under the side-bar option for tables. Pick the Links table, and then properties, and look at the drop-down box at the bottom.

Performance issues like this are really something Alex should address, as he's got loads of stats and info in his head on this.

Did you leave a support email for help? That is the best way to get a direct answer for this specific technical problem :)


PUGDOG� Enterprises, Inc.

The best way to contact me is to NOT use Email.
Please leave a PM here.
Quote Reply
Re: [Scampi] MS SQL 2000 slow as hell (yes I know its MS!) In reply to
In your LinksSQL admin, go to database and then choose Links->Properties. At the bottom of the page there should be a drop down field to select the type of indexing scheme.

However, I just tried a search on your site and got the error

Quote:


A fatal error has occured:
Can't call method "prepare" without a package or object reference at GT::SQL::Driver::prepare line 158.


Please enable debugging in setup for more details.


So I'm not sure whether there is another problem that needs fixing.
Quote Reply
Re: [afinlr] MS SQL 2000 slow as hell (yes I know its MS!) In reply to
Thanks to afinlr and pugdog for your replys! :)

I've changed the indexing to Internal, and indeed the speed of the Add.cgi and other pages has increased, but now the search doesn't work! Unsure
Scampi.

-----------------------------------------------------
A Little Madness Now And Then,
Is Relished By The Wisest Men
- Mr Willy Wonka
-----------------------------------------------------
Quote Reply
Re: [Scampi] MS SQL 2000 slow as hell (yes I know its MS!) In reply to
I've just tried it again and it seems to be working now?
Quote Reply
Re: [afinlr] MS SQL 2000 slow as hell (yes I know its MS!) In reply to
Intimitintly, I get the following error...

A fatal error has occured:

Can't call method "prepare" without a package or object reference at GT::SQL::Driver::prepare line 158.

Please enable debugging in setup for more details.

Stack Trace
======================================
Links (9316): Links::environment called at d:/virtualservers/055871/thebigtravelguide/cgi-bin/links/admin/Links.pm line 431 with no arguments.
Links (9316): Links::fatal called at GT::SQL::Driver::prepare line 158 with arguments
(Can't call method "prepare" without a package or object reference at GT::SQL::Driver::prepare line 158.
).
Links (9316): GT::SQL::Driver::prepare called at d:/virtualservers/055871/thebigtravelguide/cgi-bin/links/admin/GT/SQL/Driver/ODBC.pm line 74 with arguments
(GT::SQL::Driver::ODBC=HASH(0x21680bc), SELECT Word_ID, Frequency FROM lsql_Category_Word_List WHERE Word LIKE 'london').
Links (9316): GT::SQL::Driver::ODBC::prepare called at d:/virtualservers/055871/thebigtravelguide/cgi-bin/links/admin/GT/SQL/Table.pm line 949 with arguments
(GT::SQL::Driver::ODBC=HASH(0x21680bc), SELECT Word_ID, Frequency FROM lsql_Category_Word_List WHERE Word LIKE 'london').
Links (9316): GT::SQL::Table::do_query called at d:/virtualservers/055871/thebigtravelguide/cgi-bin/links/admin/GT/SQL/Search/INTERNAL/Search.pm line 571 with arguments
(Links::Category=HASH(0x2290e68), SELECT Word_ID, Frequency FROM lsql_Category_Word_List WHERE Word LIKE 'london').
Links (9316): GT::SQL::Search::INTERNAL::Search::_get_wordid called at d:/virtualservers/055871/thebigtravelguide/cgi-bin/links/admin/GT/SQL/Search/INTERNAL/Search.pm line 539 with arguments
(GT::SQL::Search::INTERNAL::Search=HASH(0x222da94), HASH(0x205befc)).
Links (9316): GT::SQL::Search::INTERNAL::Search::get_wordids called at d:/virtualservers/055871/thebigtravelguide/cgi-bin/links/admin/GT/SQL/Search/INTERNAL/Search.pm line 54 with arguments
(GT::SQL::Search::INTERNAL::Search=HASH(0x222da94), HASH(0x205befc), keywords).
Links (9316): GT::SQL::Search::INTERNAL::Search::_query called at d:/virtualservers/055871/thebigtravelguide/cgi-bin/links/admin/GT/SQL/Search/Base/Search.pm line 138 with arguments
(GT::SQL::Search::INTERNAL::Search=HASH(0x222da94), HASH(0x223d9d4), HASH(0x21ecb6c)).
Links (9316): GT::SQL::Search::Base::Search::query called at GT::SQL::Base::build_query_cond line 430 with arguments
(GT::SQL::Search::INTERNAL::Search=HASH(0x222da94)).
Links (9316): GT::SQL::Base::build_query_cond called at GT::SQL::Table::_query line 775 with arguments
(Links::Category=HASH(0x2290e68), HASH(0x224e7e8), HASH(0x22898e4)).
Links (9316): GT::SQL::Table::_query called at GT::SQL::Table::query_sth line 754 with arguments
(Links::Category=HASH(0x2290e68), HASH(0x2293ba4)).
Links (9316): GT::SQL::Table::query_sth called at d:/virtualservers/055871/thebigtravelguide/cgi-bin/links/admin/Links/User/Search.pm line 116 with arguments
(Links::Category=HASH(0x2290e68), HASH(0x2293ba4)).
Links (9316): Links::User::Search::query called at d:/virtualservers/055871/thebigtravelguide/cgi-bin/links/admin/GT/Plugins.pm line 108 with arguments
(HASH(0x2294620)).
Links (9316): GT::Plugins::dispatch called at d:/virtualservers/055871/thebigtravelguide/cgi-bin/links/admin/Links/User/Search.pm line 67 with arguments
(GT::Plugins, d:/virtualservers/055871/thebigtravelguide/cgi-bin/links/admin/Plugins, search_results, CODE(0x1a81074), HASH(0x2294620)).
Links (9316): Links::User::Search::search called at d:/virtualservers/055871/thebigtravelguide/cgi-bin/links/admin/Links/User/Search.pm line 48 with no arguments.
Links (9316): Links::User::Search::handle called at d:/virtualservers/055871/thebigtravelguide/cgi-bin/for querying: $VAR = {
'keywords_must' => {
'london' => {
'keyword' => '1',
'mode' => 'must',
'phrase' => undef,
'substring' => undef
}
}
};
Links::Category (9316): Query: SELECT Word_ID, Frequency FROM lsql_Category_Word_List WHERE Word LIKE 'london'
GT::SQL::Driver::ODBC (9316): Preparing query: SELECT Word_ID, Frequency FROM lsql_Category_Word_List WHERE Word LIKE 'london' at GT::SQL::Driver::prepare line 157.
Can't call method "prepare" without a package or object reference at GT::SQL::Driver::prepare line 158.
links/admin/GT/Plugins.pm line 108 with no arguments.
Links (9316): GT::Plugins::dispatch called at d:\virtualservers\055871\thebigtravelguide\cgi-bin\links\search.cgi line 25 with arguments
(GT::Plugins, d:/virtualservers/055871/thebigtravelguide/cgi-bin/links/admin/Plugins, handle_search, CODE(0x1982944)).
System Information
======================================
Perl Version: 5.006001
Links SQL Version: 2.1.2
DBI.pm Version: 1.37
Persistant Env: mod_perl (0) SpeedyCGI (0)
GT::SQL::error = Could not connect to database. Reason:
DBI::errstr = [Microsoft][ODBC SQL Server Driver]Timeout expired (SQL-HYT00)(DBD: db_login/SQLConnect err=-1)
@INC =
d:/virtualservers/055871/thebigtravelguide/cgi-bin/links/admin
D:/Perl/lib
D:/Perl/site/lib
.
CGI INPUT
======================================
isValidated => Yes
query => London
ENVIRONMENT
======================================
ALLUSERSPROFILE => C:\Documents and Settings\All Users
COMMONPROGRAMFILES => C:\Program Files\Common Files
COMPUTERNAME => INETC107
COMSPEC => C:\WINNT\system32\cmd.exe
CONTENT_LENGTH => 0
DISKEEPERICON => C:\Program Files\Executive Software\DiskeeperServer\
GATEWAY_INTERFACE => CGI/1.1
GT_TMPDIR => d:/virtualservers/055871/thebigtravelguide/cgi-bin/links/admin/tmp
HTTPS => off
HTTP_ACCEPT => text/html, image/png, image/jpeg, image/gif, image/x-xbitmap, */*;q=0.1
HTTP_ACCEPT_CHARSET => windows-1252, utf-8, utf-16, iso-8859-1;q=0.6, *;q=0.1
HTTP_ACCEPT_ENCODING => deflate, gzip, x-gzip, identity, *;q=0
HTTP_ACCEPT_LANGUAGE => en
HTTP_CACHE_CONTROL => no-cache
HTTP_CONNECTION => Keep-Alive, TE
HTTP_HOST => www.thebigtravelguide.com
HTTP_TE => deflate, gzip, chunked, identity, trailers
HTTP_USER_AGENT => Mozilla/4.0 (compatible; MSIE 6.0; MSIE 5.5; Windows NT 5.1) Opera 7.03 [en]
INSTANCE_ID => 767
LOCAL_ADDR => 195.224.253.126
NUMBER_OF_PROCESSORS => 1
OS => Windows_NT
OS2LIBPATH => C:\WINNT\system32\os2\dll;
PATH => D:\Perl\bin\;C:\WINNT\system32;C:\WINNT;C:\WINNT\System32\Wbem;C:\Program Files\Symantec\pcAnywhere\;C:\MSSQL7\BINN;c:\batch;C:\Program Files\Executive Software\DiskeeperServer\
PATHEXT => .COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WSF;.WSH;.VBS
PATH_INFO => /cgi-bin/links/search.cgi
PATH_TRANSLATED => d:\virtualservers\055871\thebigtravelguide\cgi-bin\links\search.cgi
PROCESSOR_ARCHITECTURE => x86
PROCESSOR_IDENTIFIER => x86 Family 6 Model 8 Stepping 10, GenuineIntel
PROCESSOR_LEVEL => 6
PROCESSOR_REVISION => 080a
PROGRAMFILES => C:\Program Files
QUERY_STRING => query=London
REMOTE_ADDR => 81.101.216.29
REMOTE_HOST => 81.101.216.29
REQUEST_METHOD => GET
SCRIPT_NAME => /cgi-bin/links/search.cgi
SERVER_NAME => www.thebigtravelguide.com
SERVER_PORT => 80
SERVER_PORT_SECURE => 0
SERVER_PROTOCOL => HTTP/1.1
SERVER_SOFTWARE => Microsoft-IIS/5.0
SYSTEMDRIVE => C:
SYSTEMROOT => C:\WINNT
TEMP => C:\WINNT\TEMP
TMP => C:\WINNT\TEMP
USERPROFILE => C:\Documents and Settings\Default User
WINDIR => C:\WINNT
Scampi.

-----------------------------------------------------
A Little Madness Now And Then,
Is Relished By The Wisest Men
- Mr Willy Wonka
-----------------------------------------------------
Quote Reply
Re: [Scampi] MS SQL 2000 slow as hell (yes I know its MS!) In reply to
There is also an issue with this...



A fatal error has occured:

Can't call method "fetchrow_array" on an undefined value at d:/virtualservers/055871/thebigtravelguide/cgi-bin/links/admin/Links/Link.pm line 573.

Please enable debugging in setup for more details.

Stack Trace
======================================
Links (10540): Links::environment called at d:/virtualservers/055871/thebigtravelguide/cgi-bin/links/admin/Links.pm line 431 with no arguments.
Links (10540): Links::fatal called at d:/virtualservers/055871/thebigtravelguide/cgi-bin/links/admin/Links/Link.pm line 573 with arguments
(Can't call method "fetchrow_array" on an undefined value at d:/virtualservers/055871/thebigtravelguide/cgi-bin/links/admin/Links/Link.pm line 573.
).
Links (10540): Links::Link::HTML::get_all_categories called at d:/virtualservers/055871/thebigtravelguide/cgi-bin/links/admin/Links/User/Add.pm line 195 with arguments
(Links::Link::HTML=HASH(0x22b542c), ARRAY(0x1a8de00), CatLinks.CategoryID, 1).
Links (10540): Links::User::Add::_category_list called at d:/virtualservers/055871/thebigtravelguide/cgi-bin/links/admin/Links/User/Add.pm line 55 with no arguments.
Links (10540): Links::User::Add::handle called at d:/virtualservers/055871/thebigtravelguide/cgi-bin/links/admin/GT/Plugins.pm line 108 with no arguments.
Links (10540): GT::Plugins::dispatch called at d:\virtualservers\055871\thebigtravelguide\cgi-bin\links\add.cgi line 25 with arguments
(GT::Plugins, d:/virtualservers/055871/thebigtravelguide/cgi-bin/links/admin/Plugins, handle_add, CODE(0x1982944)).
System Information
======================================
Perl Version: 5.006001
Links SQL Version: 2.1.2
DBI.pm Version: 1.37
Persistant Env: mod_perl (0) SpeedyCGI (0)
GT::SQL::error = Could not connect to database. Reason:
DBI::errstr = [Microsoft][ODBC SQL Server Driver]Timeout expired (SQL-HYT00)(DBD: db_login/SQLConnect err=-1)
@INC =
d:/virtualservers/055871/thebigtravelguide/cgi-bin/links/admin
D:/Perl/lib
D:/Perl/site/lib
.
CGI INPUT
======================================
ID => Unknown Tag: 'category_id'
ENVIRONMENT
======================================
ALLUSERSPROFILE => C:\Documents and Settings\All Users
COMMONPROGRAMFILES => C:\Program Files\Common Files
COMPUTERNAME => INETC107
COMSPEC => C:\WINNT\system32\cmd.exe
CONTENT_LENGTH => 0
DISKEEPERICON => C:\Program Files\Executive Software\DiskeeperServer\
GATEWAY_INTERFACE => CGI/1.1
GT_TMPDIR => d:/virtualservers/055871/thebigtravelguide/cgi-bin/links/admin/tmp
HTTPS => off
HTTP_ACCEPT => text/html, image/png, image/jpeg, image/gif, image/x-xbitmap, */*;q=0.1
HTTP_ACCEPT_CHARSET => windows-1252, utf-8, utf-16, iso-8859-1;q=0.6, *;q=0.1
HTTP_ACCEPT_ENCODING => deflate, gzip, x-gzip, identity, *;q=0
HTTP_ACCEPT_LANGUAGE => en
HTTP_CONNECTION => Keep-Alive, TE
HTTP_HOST => www.thebigtravelguide.com
HTTP_IF_MODIFIED_SINCE => Wed, 13 Aug 2003 16:55:05 GMT
HTTP_TE => deflate, gzip, chunked, identity, trailers
HTTP_USER_AGENT => Mozilla/4.0 (compatible; MSIE 6.0; MSIE 5.5; Windows NT 5.1) Opera 7.03 [en]
INSTANCE_ID => 767
LOCAL_ADDR => 195.224.253.126
NUMBER_OF_PROCESSORS => 1
OS => Windows_NT
OS2LIBPATH => C:\WINNT\system32\os2\dll;
PATH => D:\Perl\bin\;C:\WINNT\system32;C:\WINNT;C:\WINNT\System32\Wbem;C:\Program Files\Symantec\pcAnywhere\;C:\MSSQL7\BINN;c:\batch;C:\Program Files\Executive Software\DiskeeperServer\
PATHEXT => .COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WSF;.WSH;.VBS
PATH_INFO => /cgi-bin/links/add.cgi
PATH_TRANSLATED => d:\virtualservers\055871\thebigtravelguide\cgi-bin\links\add.cgi
PROCESSOR_ARCHITECTURE => x86
PROCESSOR_IDENTIFIER => x86 Family 6 Model 8 Stepping 10, GenuineIntel
PROCESSOR_LEVEL => 6
PROCESSOR_REVISION => 080a
PROGRAMFILES => C:\Program Files
QUERY_STRING => ID=Unknown%20Tag:%20'category_id'
REMOTE_ADDR => 81.101.216.29
REMOTE_HOSH(0x22b542c), ARRAY(0x1a8de00), CatLinks.CategoryID, 1).
GT::Base (10540): Links::User::Add::_category_list called at d:/virtualservers/055871/thebigtravelguide/cgi-bin/links/admin/Links/User/Add.pm line 55 with no arguments.
GT::Base (10540): Links::User::Add::handle called at d:/virtualservers/055871/thebigtravelguide/cgi-bin/links/admin/GT/Plugins.pm line 108 with no arguments.
GT::Base (10540): GT::Plugins::dispatch called at d:\virtualservers\055871\thebigtravelguide\cgi-bin\links\add.cgi line 25 with arguments
(GT::Plugins, d:/virtualservers/055871/thebigtravelguide/cgi-bin/links/admin/Plugins, handle_add, CODE(0x1982944)).
Can't call method "fetchrow_array" on an undefined value at d:/virtualservers/055871/thebigtravelguide/cgi-bin/links/admin/Links/Link.pm line 573.
T => 81.101.216.29
REQUEST_METHOD => GET
SCRIPT_NAME => /cgi-bin/links/add.cgi
SERVER_NAME => www.thebigtravelguide.com
SERVER_PORT => 80
SERVER_PORT_SECURE => 0
SERVER_PROTOCOL => HTTP/1.1
SERVER_SOFTWARE => Microsoft-IIS/5.0
SYSTEMDRIVE => C:
SYSTEMROOT => C:\WINNT
TEMP => C:\WINNT\TEMP
TMP => C:\WINNT\TEMP
USERPROFILE => C:\Documents and Settings\Default User
WINDIR => C:\WINNT
Scampi.

-----------------------------------------------------
A Little Madness Now And Then,
Is Relished By The Wisest Men
- Mr Willy Wonka
-----------------------------------------------------
Quote Reply
Re: [Scampi] MS SQL 2000 slow as hell (yes I know its MS!) In reply to
Hi,

If you are on MS SQL, you should definitely use the MS SQL indexing method. This is by far the fastest search on MSSQL (as it uses the databases servers own full text indexing).

It does require that the full text indexing module be installed on the server. If you are having problems getting it going, please send me an email with terminal services/vnc access and I'll take a look at your setup.

Cheers,

Alex
--
Gossamer Threads Inc.
Quote Reply
Re: [Alex] MS SQL 2000 slow as hell (yes I know its MS!) In reply to
Thanks, but sadly we don't have that access to the system :(



Can someone please look over the error message above? I need to get these sorted, as while there not appearing every few mins, they do pop up more than I would like them to, and more than I expect them to.

Thanks for everyones time!



Chris Williams (cwi@workslikeadream.com)
Quote Reply
Re: [Scampi] MS SQL 2000 slow as hell (yes I know its MS!) In reply to
Sorry! Sorry! I'm being a fool!

I've just seen the reasons for both those errors. Time outs. Dam them! Wink



Chris Williams (cwi@workslikeadream.com)
Quote Reply
Re: [Scampi] MS SQL 2000 slow as hell (yes I know its MS!) In reply to
Can you tell me what version of perl you are using on your win2000 server?



Thank you.

--Ron Dippold