I'm new in databases and SQL queries.
Q1. Do you know the common problems one generally face in database queries? Most likely very large queries, performance issues, timeout and connectivity errors, export import and restoration errors
Q2. What approach and tools you ought to be familiar with while debugging a follow up query performance?
It will likely be great if you're able to share a few of the problems faced and just how have you went forward for solving it.
Most likely probably the most essential things you will need to seem to comprehend is indexes. One large potential bottleneck for query performance is when you're looking for a column that's not a part of a catalog also it leads to a table scan. A table scan implies that every row has been scanned, so naturally the greater records within the table(s), the worse the performance.
I am unsure what database you're using. however , from the SQL Server perspective you'd be well offered finding out how to use SQL Profiler. You may also see the execution plan of the query through SQL Management Studio, this can explain in which the performance problems inside your query may be.
Should you truly are curious about performance tuning database queries you need a large body fat book about them for that database of your liking. Performance tuning is extremely database specific. Nevertheless, one commmon problem in tuning queries that you simply should be aware regardless of database would be that the updated query should return the identical result set because the original query but be faster. Sometimes people tuning forget this fundamental factor and finish track of a question that's faster but does not perform the same factor.
Problems in database queries -
Wrong results - the query does not really do what you would like it to complete
Performance - the query occasions out or perhaps is not fast enough. A lot of things may cause this but a few common causes can be harmful database design (don't use EAV tables for example), utilization of poorly carrying out query techinques for example correlated subqueries, looping through records individually, insufficient correct indexing, statistics outdated, queries that aren't sargeable not to mention hardware and network issues.
For Card inserts,updates, removes - there might be issues with data integrity because of poor database design (insufficient foreign secrets, insufficient default values, insufficient constraints) or query design. SOmetimes people decide to make thier own autoincrementing area rather than using whatever has the datbase. These may encounter concurrency issues.
Security - database doesn't correctly safeguard it's data (file encryption of private data, coding to prevent injection attacks, restricting privileges to complete items to the dat, etc.)
Insufficient testing of edge cases - this can be a frequent condition in dynamic SQl and triggers particularly when designers only consider the most typical situation and the one thing breaks once the edge situation hits it.
The most crucial factor for debugging SQL Queries is SQL Server Profiler http://msdn.microsoft.com/en-us/library/ms187929.aspx
This provides you with reads, creates, time spent, etc.
Execution plans will also be very useful and will highlight whether or not this did a scan rather than a seek or the other way around. Consider these too http://msdn.microsoft.com/en-us/library/ms178071.aspx