I have a case where using a JOIN or an IN will give me the correct results... Which typically has better performance and why? How much does it depend on what database server you are running? (FYI I am using MSSQL) Thanks ...
Hi, I have two queries to get related tags from a mysql database, one works, one does not, my question is: "why?" Problem: When executing the first query, the mysql server gets 100% cpu usage, and has to be restarted to function again. Query 1 (does not work): SELECT tags.*, COUNT(ct.company_id) AS count FROM company2tag ct, tags WHE...
I have created a single table in my primary DB called tblGlobalIDMapping that will assign a GUID to all entries and store the other 3 primary ID's across the App to provide for a single ID repository. The new Table is in the following structure --> AppGlobalID uniqueidentifier NoNulls PersonID int NoNul...
I'm trying to figure out how I can check if a database role exists in SQL Server. I want to do something like this: if not exists (select 1 from sometable where rolename='role') begin CREATE ROLE role AUTHORIZATION MyUser; end What table/proc should I use here? ...
Hey all, I've got a query in need of optimizing. It works but its a dog, performance wise. It reads like this: SELECT * FROM ( SELECT * FROM views WHERE user_id = '1' ORDER BY page DESC ) v GROUP BY v.session I'm tracking views to different pages, and I want to...
For INSERT, UPDATE and DELETE SQL statements executed directly against the database, most database providers return the count of rows affected. For stored procedures, the number of records affected is always -1. How do we get the number of records affected by a stored procedure? ...
Whenever the value is null for this query SELECT ISNULL(someDateTime,'') FROM someTable the result is someDateTime ------------ 1900-01-01 00:00:00.000 I want it to be "No", so if I run this: SELECT ISNULL(someDateTime,'No') FROM someTable then there's this error: Conversion failed when converting datetime from characte...
I'm getting my head back into SubSonic on a project with v2.1 fairly embedded (meaning we won't be switching it to v3). I'm ripping through a bunch of method parameters to build a long, but not overly complex, query. At the tail end of this query, I need to add a statement that adds a group of OR statements, something to the equivalent...
Hi folks! Can somebody give a hint on this one? : I have a table, let's say tblA, where I have id1 and id2 as columns and index(id1,id2). I want to select the id1´s where id2´s belong to several sets. So I would want to say select id1 from tblA where id2 in (val1,val2,val3 ...) union select id1 from tblA where id2 in (val4,val2,val3...
I'm using a single textbox to search a report and filter out records. One of my fields is an int32 and the rest are varchar's. So when the filter tries to compare the string from the textbox with the int32 field, I get an error. Here's the SQLDataSouce and the search box: <asp:TextBox ID="SearchBox" AutoPostBack="true" OnTextChanged="Se...
Is there any tool that will inspect either asp.net or sql server and report all the queries that are run against the database? The reason I ask is I am using Linq for a project and want to double check what its actually doing for each page. Ideally I'd like to view a page in a browser and have a report of all the queries that were ru...
I have a SQL query running using something like this: PreparedStatement pstmt = dbConn.prepareStatement(sqlQuery); ResultSet rs = null; .. .. Set parms here .. .. rs = pstmt.executeQuery(); It works fine when I use a local database, but I have to test on this remote one since it has a lot more data. But since it's remote, it is taking...
I identified a bug in my code and I'm baffled as to how it could have occurred in the first place. My question is, I found a skip in the database ID fields (it is an incremented identity field), indicating some records weren't inserted - which means my SQL sprocs (probably) threw an error. Thinking backwards from there, that means that ...
I have a script which generates queries in the following fashion (based on user input): SELECT * FROM articles WHERE (articles.skeywords_auto ilike '%pm2%') AND spubid IN ( SELECT people.spubid FROM people WHERE (people.slast ilike 'chow') GROUP BY people.spubid) LIMIT 1; The resulting data set: Array ( [0] => Ar...
I had a working FREETEXTTABLE query that searched for a @searchString. I now need to UNION that with another simple query that tries to parse the @searchString into an INT, and if it succeeds, filtering the table by looking for the row with PK equal to the parse @searchString. Previously, I could easily JOIN the FREETEXTTABLE result to ...
I have two tables in a MySQL database, courses and sessions. I'm trying to summarise the entries in sessions for each course each month. I can summarise the total sessions for each course, no problem using this query: SELECT courses.CourseID, SUM(IF( sessions.Duration IS NULL , 0, sessions.Duration)) AS Hrs FROM courses ...
I have a table in SQL Server 2005 with the following structure: fname | id ---------- aaa | bbb | ccc | I need a query that will fill the id column with sequential numbers (eg, 1-3 for this example data). I need this to update the table itself, not just a query that shows this in the results. ...
I want to save new data from access tables to sql tables without overwriting old data in sql. Data table format for access and sql is same.(using Visual Basic) ...
Is there a way to query SQL Server XML type so that for an element with xsi:nil="true", return null instead of default datetime value, which is 1900-01-01 00:00:00.000? here is a code snippet declare @data xml set @data = '<DOD xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />' select Value1 = @data.value('/...
To preface this, I'm not familiar with OLAP at all, so if the terminology is off, feel free to offer corrections. I'm reading about OLAP and it seems to be all about trading space for speed, wherein you precalculate (or calculate on demand) and store aggregations about your data, keyed off by certain dimensions. I understand how this wo...