Any good(free/Open Source) sql query performance Analyzer tool..
I am using sql server 2005 and i want to measure some query performance using an analyzer tool.... Any good tool (free/Open Source) that you can refer..... ...
sql
Help converting an sql query into LINQ
Hey, I'm trying to convert some SQL I've patched together into a LINQ query. The context is a simple quiz app. A quiz has quizrounds, contestants are one to one attached to quizrounds, rounds have registered answers. The case is that I have a one to many relationship between tables 'quizround' and 'answer'. I want to do a ranking of a...
query optimization
I have a query of the form SELECT uid1,uid2 FROM friend WHERE uid1 IN (SELECT uid2 FROM friend WHERE uid1='.$user_id.') and uid2 IN (SELECT uid2 FROM friend WHERE uid1='.$user_id.') The problem now is that the nested query SELECT uid2 FROM friend WHERE uid1='.$user_id.' returns a very large number of ids(approx. 5000). The ...
Is there a severe performance hit for using Foreign Keys in SQL Server?
I'm trying my best to persuade my boss into letting us use foreign keys in our databases - so far without luck. He claims it costs a significant amount of performance, and says we'll just have jobs to cleanup the invalid references now and then. Obviously this doesn't work in practice, and the database is flooded with invalid reference...
Simplify a MySQL query
Hi, I have below a working query that needs to be simplified. The reason is that I need to expand it a lot to cover the real application. For each condition (Pos=xxx AND Indata=yyy) the current query doubles in size, and I have a lot of conditions. Also the ON clause will contain many more conditions than in the example.... The real a...
Joining 2 SQL SELECT result sets into one
Hi. I've got 2 select statements, returning data like this: Select 1 col_a col_b Select 2 col_a col_c If I do union, I get something like col_a col_b And rows joined. What i need is getting it like this: col_a col_b col_c Joined on data in col_a. ...
List tables involved in a sql statement before it is run?
Is it possible, in .NET, to pass a sql statement to SQL server for parsing and return the tables involved in the statement and the type of operation. So for a statement like this : select * from Table1 left outer join Table2 on Table1.id=Table2.foreignid; delete from Table2 where date < 2009/12/12 SQL Server might return the tables in...
SQL: Get latest record
Hi all, this is my relational model: Request ------------------------------ RequestId ------------------------------ 1 2 RequestState ------------------------------ RequestStateId | Name ------------------------------ 10 | Received 20 | Processing 30 | Finsihed Request...
Advice on Change Tracking in Sql Server 2008
My client is looking for a way to do a full audit trails (full view of historical data on all tables) on the application. Other than using the old fashioned way of having table copies or storing field name, field value, modified by, modified on etc, I was looking at using Sql Server 2008 change tracking. Found a How-To article on msdn ...
ADODB connection to run SQL Server Procedure
I'm currently trying to run a certain procedure (sql 2005) in VB6, passing some parameters: Dim conn As Connection Set conn = New Connection conn.Open "Provider=whateverprovider;Data Source=whateversource;Database=whateverdatabase;User Id=whateverID;Password=whatever" Dim CMD As ADODB.Command Dim rs As ADODB.Recordset Set CMD = New ADOD...
How can I limit number of results by a specific column in postgreSQL?
Hi, I have a table with user items. Each user may have several types of items, and may have each item more than once. I want to see how many items of each type each user have. So I use the following query: select user_name, count(item_name) as "count_item", item_name from my_table group by user_name, item_name order by user_name, co...
How to find SQL Server queries that took a lot of time?
I have application that is up more than 3 days. I can see in logs that there was a moment when application executed some SQL query and this took a lot of time, probably because of some db locks. I heard that there is a query for such situations. So I need to be able to ask all queries that took, for example, more than 30 minutes. Is i...
Paging and custom-ordering a result
Hello, I want to query a table so that it´s ordered the following way: 1) "entry" 2) "entry#" 3) "entry something" 4) "..entry.." I did this via Union All and 4 different queries. But additionally, I want to include paging, so that I can f.ex. receive the row-numbers 1-100, 101-200 etc. I tried Row_Num() Over (Order By) but did not ge...
SQL - XOR based on referencing table
Hi. Say for example, I have the following two tables: TableA { _id } TableB { _id, TableA_id, OptionString } Each row in TableA has one or more rows in TableB referencing it (using the TableA_id foreign key). My query lists rows in TableA to display to the user. OptionString can be one of two values - let's say "Option1" and "Optio...
How to update table in database1 from function in database2 in PostgreSQL?
Hi, Is there a way to update a table in database1 from a function in database2 (both databases are on the same server)? Basically cross database update in PostgreSQL. Function is executed by a trigger but it shouldn't matter. -= edit =- I know I can make it using DBLink but I would like to modify the source database as little as possi...
How to query Webtrends ODBC from Sql Server 2000
I have a webtrends installation. i would like to query the webtrends and show the stats on my webpage. can i do it using linked servers (couldn't find the option for webtrends) or can i do it using openrowset (tried but it doesn't work) -Vivek ...
How to SELECT immediate children and ancestors all in the same query
I'm working with a tree structure in MySQL that is respresented using the nested sets model. I'm hoping some of you sql experts can help me with building a SELECT query. I would like to be able to match a set of nodes using LIKE. For each node that is matched, I also need a comma-delimmited list of the ancestors of that node, and a co...
SQL/MySQL tutorial to practice usage of commands online.
I have practiced SQL in w3schools, which is good but doesn’t allow to try with DDL commands, which is certainly not satisfying. Please suggest me such tutorial(s), which allow to practice SQL or MySQL online, allowing to use all the commands, if you have ever observed. ...
SQL recursive query on self refrencing table (Oracle)
Hello Lets assume I have this sample data: | Name | ID | PARENT_ID | ----------------------------- | a1 | 1 | null | | b2 | 2 | null | | c3 | 3 | null | | a1.d4 | 4 | 1 | | a1.e5 | 5 | 1 | | a1.d4.f6 | 6 | 4 | | a1.d4.g7 | 7 | 4 | | a1.e5.h8 | 8 | 5 ...
Is jumping across servers necessarily a bad programming practice?
I have a system created that a user at one of our other locations and on their server inserts a record. That data is then replicated to a central server. Users working on the central server are allowed to edit that record which means I have to lock the editing capabilities at the location the record was created. However, i would like t...