SQl problem

hello all,

I don't know where should I need to ask this question.... I am having some weird problem with SQL server 2000... sometimes it is woking fine and retrieving data within a second...and sometime the same query I type in SQL analyser and it takes more than 5mins to display the data...I made few indexes on fields.... can anyone help me please with this problem?is there any internal setting in SQL that I need to do?.. I have 5million records in my database...

radhika...

[500 byte] By [radhika_exceptiona] at [2007-10-3 4:28:50]

«« Casting Object to Integer
»» dispose() throwing exception?

# 1

A good place to ask this would be at some forum or newsgroup that deals with your database.

CeciNEstPasUnProgrammeura at 2007-7-14 22:31:50 >

top of Java-index,Java Essentials,Java Programming...

# 2

I suggest getting a) a competent DBAb) some realistic expectations. Querying a 5 million row table is going to take some time. Complex queries will take more time.

cotton.ma at 2007-7-14 22:31:50 >

# 3

If you are seeing such extremes, perhaps you are experiencing locking contentions or maybe network issues.

jbisha at 2007-7-14 22:31:50 >

# 4

Is it always the same WHERE clause? If not, it could be that some of your queries involve simple equality on indexed fields and therefore run fast, while others require a full table scan and run very slowly because they use LIKE or access unindexed fields.

btw, MSDN has a SQL Server forum, although tey may have forgotten about SQL2000. Have you tried this on SQL2005? The developer edition is a full enterprise edition, and it costs about $50 US. The Express edition is free.

pkwoostera at 2007-7-14 22:31:50 >

# 5

guys,

thatnks for the quick reply.....my problem is this, problem stays for few mins only... so after a while if I run the same query it get me data faster...is that a possibility that some of my connection is creating problem?... I am opening and closing connection in my program frequently...

please reply..

thanks

Radhika.

radhika_exceptiona at 2007-7-14 22:31:50 >

# 6

I've seen SQL Server have serious troubles keeping its statistics up-to-date for a large database. One process that I used to support would go from 16 minutes one day to several hours the next. I believe the DBA fixed it by adding one or more scheduled UPDATE STATISTICS tasks throughout the day.

Brian

brian@cubik.caa at 2007-7-14 22:31:50 >

# 7

There are several ways I can think of the top of my head to speed up getting query results:

1) Use store procedure

2) Use materialized view

3) Use prepared statement

4) Make sure your database is properly normalized (which could lead to smaller tables)

5) Index the field you are searching on (B+ Tree if range predicate and Hash/B+ Tree for equality)

...

tymer99a at 2007-7-14 22:31:50 >

# 8

> my problem is this,

> problem stays for few mins only... so after a while

> if I run the same query it get me data faster...is

> that a possibility that some of my connection is

> creating problem?... I am opening and closing

> connection in my program frequently...

It usually takes longer for the first query to run than subsequent executions of the same query. A query plan has to be generated the first time which can be expensive.

tymer99a at 2007-7-14 22:31:50 >

Java programming resources: FAQs, tutorials, compiler and browser download sites, documentation, books lists, IDEs, etc.

SQl problem

Java Essentials New Topic

Java Essentials Hot Topic

Java Essentials

All Classified