Search Results

Search found 37012 results on 1481 pages for 'sql query'.

Page 295/1481 | < Previous Page | 291 292 293 294 295 296 297 298 299 300 301 302  | Next Page >

• SQL Server Mutliple Joins Taxing CPU

  - by durilai

  I have a stored procedure on SQL server 2005. It is pulling from a Table function, and has two joins. When the query is run using a load test it kills the CPU 100% across all 16 cores! I have determined that removing one of the joins makes the query run fine, but both taxes the CPU. Select SKey From dbo.tfnGetLatest(@ID) a left join [STAGING].dbo.RefSrvc b on a.LID = b.ESIID left join [STAGING].dbo.RefSrvc c on a.EID = c.ESIID Any help is appreciated, note the join is happening on the same table in a different database on the same server.

  Read the article

• Web service performance testing plan, Microsoft .NET WS, SQL

  - by zxed

  Trying to answer a question to come up with a testing plan. It has to do with using a website and/or webservice that queries a sql server to get data and display to user. * Solution must be able to handle an estimated 2000 users, approximately 700 concurrent users, 10,000 + website hits a month. Database calls should handle 100,000 queries via the website/webservice a month. The system is used at multiple times during a 24 hour period; however networking and bandwidth traffic decreases after 5 pm * two windows 2003 servers are used, one for web, another for sql. Both are located in the same room. User access is varied and users can be far/near (its a centralized system), users access via www

  Read the article

• NHibernate, the Parallel Framework, and SQL Server

  - by andy

  hey guys, we have a loop that: 1.Loops over several thousand xml files. Altogether we're parsing millions of "user" nodes. 2.In each iteration we parse a "user" xml, do custom deserialization 3.finally, in each iteration, we send our object to nhibernate for saving. We use: .SaveOrUpdateAndFlush(user); This is a lengthy process, and we thought it would be a perfect candidate for testing out the .NET 4.0 Parallel libraries. So we wrapped the loop in a: Parallel.ForEach(); After doing this, we start getting "random" Timeout Exceptions from SQL Server, and finally, after leaving it running all night, OutOfMemory unhandled exceptions. I haven't done deep debugging on this yet, but what do you guys think. Is this simply a limitation of SQL Server, or could it be our NHibernate setup, or what? cheers andy

  Read the article

• T-SQL - how to get around the order by restriction in CTEs

  - by David

  Hi all I have the following CTE. Its purpose is to provide unique Month/Year pairs. Later code will use the CTE to produce a concatenated string list of the Month/Year pairs. ;WITH tblStoredWillsInPeriod AS ( SELECT DISTINCT Kctc.GetMonthAndYearString(DateWillReceived) Month FROM Kctc.StoredWills WHERE DateWillReceived BETWEEN '2010/01/01' AND '2010/03/31' ORDER BY DateWillReceived ) I have omitted the implmementation of the GetMonthAndYearString function as it is trivial. Sadly, it seems T-SQL is always one step ahead. When I run this code, it tells me I'm not allowed to use ORDER BY in a CTE unless I also use TOP (or FOR XML, whatever that is.) If I use TOP, it tells me I can't use it with DISTINCT. Yup, T-SQL has all the answers. Can anyone think of a solution to this problem which is quicker than simply slashing my wrists? I understand that death from blood loss can be surprisingly lingering, and I have deadlines to meet. Thanks for your help. David

  Read the article

• Storing website hierarchy in Sql Server 2008

  - by Mika Kolari

  I want to store website page hierarchy in a table. What I would like to achieve is efficiently 1) resolve (last valid) item by path (e.g. "/blogs/programming/tags/asp.net,sql-server", "/blogs/programming/hello-world" ) 2) get ancestor items for breadcrump 3) edit an item without updating the whole tree of children, grand children etc. Because of the 3rd point I thought the table could be like ITEM id type slug title parentId 1 area blogs Blogs 2 blog programming Programming blog 1 3 tagsearch tags 2 4 post hello-world Hello World! 2 Could I use Sql Server's hierarchyid type somehow (especially point 1, "/blogs/programming/tags" is the last valid item)? Tree depth would usually be around 3-4. What would be the best way to achieve all this?

  Read the article

• What's the best way to calculate similarity between rows in a table based on association?

  - by André Pena

  Suppose each Person has a collection of favorite Books. So I have a table for: Person Book The association between Person and Book (joint table for MxN) I want to fetch the Persons that are similar to a Person1 based on the favorite Books overlaping. That is: The more books they have in common, the more they are similar. I don't have to use only SQL to solve this problem. I could use programming also. I'm using SQL Server 2008 and C#. What solution would you experts use?

  Read the article

• Popularity Algorithm - SQL / Django

  - by RadiantHex

  Hi folks, I've been looking into popularity algorithms used on sites such as Reddit, Digg and even Stackoverflow. Reddit algorithm: t = (time of entry post) - (Dec 8, 2005) x = upvotes - downvotes y = {1 if x > 0, 0 if x = 0, -1 if x < 0) z = {1 if x < 0, otherwise x} log(z) + (y * t)/45000 I have always performed simple ordering within SQL, I'm wondering how I should deal with such ordering. Should it be used to define a table, or could I build an SQL with the ordering within the formula (without hindering performance)? I am also wondering, if it is possible to use multiple ordering algorithms in different occasions, without incurring into performance problems. I'm using Django and PostgreSQL. Help would be much appreciated! ^^

  Read the article

• Server authorization with MD5 and SQL.

  - by Charles

  I currently have a SQL database of passwords stored in MD5. The server needs to generate a unique key, then sends to the client. In the client, it will use the key as a salt then hash together with the password and send back to the server. The only problem is that the the SQL DB has the passwords in MD5 already. Therefore for this to work, I would have to MD5 the password client side, then MD5 it again with the salt. Am I doing this wrong, because it doesn't seem like a proper solution. Any information is appreciated.

  Read the article

• Impersonation in asp.net, confused about implmentation when used with Active Directory & Sql Server

  - by AWC

  I have an internal website that is using integrated windows authentication and this website uses sql server & active directory queries via the System.Directory.Services namespace. To use the System.Directory.Services namespace in ASP.NET I have to run IIS under an account that has the correct privileges and importantly have impersonation set to true in the web config. If this is done then when I make a query against AD then the credentials of the wroker process (IIS) are used instead of the ASPNET account and therefore the queries will now succeed. Now if I am also using Sql Server with a connection string configured for integrated security ('Integrated Security=SSPI') then this interprets the ASP.NET impersonation to mean that I want to access the database as the windows credentials of the request no the worker process. I hope I'm wrong and that I've got the config wrong, but I don't think I have and this seems not to be inconsistent? It should be noted I'm using IIS 5.1 for development and obivously this doesn't have the concept of app-pools which I believe would resolve the problem.

  Read the article

• SQL bottleneck, how to fix

  - by masfenix

  This is related to my previous thread: http://stackoverflow.com/questions/3069806/sql-query-takes-about-10-20-minutes However, I kinda figured out the problem. The problem (as described in the previous thread) is not the insert (while its still slow), the problem is looping through the data itself Consider the following code: Dim rs As DAO.Recordset Dim sngStart As Single, sngEnd As Single Dim sngElapsed As Single Set rs = CurrentDb().QueryDefs("select-all").OpenRecordset MsgBox "All records retreived" sngStart = Timer Do While Not rs.EOF rs.MoveNext Loop sngEnd = Timer sngElapsed = Format(sngEnd - sngStart, "Fixed") ' Elapsed time. MsgBox ("The query took " & sngElapsed _ & " seconds to run.") As you can see, this loop does NOTHING. You'd expect it to finish in seconds, however it takes about 857 seconds to run (or 15 minutes). I dont know why it is so slow. Maybe the lotusnotes sql driver? any other ideas? (java based solution, any other solution) What my goal is: To get all the data from remote server and insert into local access table

  Read the article

• SQL for selecting only the last item from a versioned table

  - by Jeremy

  I have a table with the following structure: CREATE TABLE items ( id serial not null, title character varying(255), version_id integer DEFAULT 1, parent_id integer, CONSTRAINT items_pkey PRIMARY KEY (id) ) So the table contains rows that looks as so: id: 1, title: "Version 1", version_id: 1, parent_id: 1 id: 2, title: "Version 2", version_id: 2, parent_id: 1 id: 3, title: "Completely different record", version_id: 1, parent_id: 3 This is the SQL code I've got for selecting out all of the records with the most recent version ids: select * from items inner join ( select parent_id, max(version_id) as version_id from items group by parent_id) as vi on items.version_id = vi.version_id and items.parent_id = vi.parent_id Is there a way to write that SQL statement so that it doesn't use a subselect?

  Read the article

• NHibernate: How to show the generated SQL-statements in the windows console

  - by Rookian

  return Fluently.Configure() .Database(MsSqlConfiguration.MsSql2008 .ConnectionString(c => c .Database(Database) .TrustedConnection() .Server(Server) ).ShowSql()) .ExposeConfiguration(c => c.SetProperty("current_session_context_class", "web")) .Mappings(m => m.FluentMappings.AddFromAssemblyOf<TeamMap>()).BuildConfiguration(); I have a web application. This configuration does not work. I also have a Console application that shall be responsible to write out all generated SQL. How can I get the generated SQL commands? Thanks in advance!

  Read the article

• What are the types and inner workings of a query optimizer?

  - by Frank Developer

  As I understand it, most query optimizers are cost-based. Some can be influenced by hints like FIRST_ROWS(). Others are tailored for OLAP. Is it possible to know more detailed logic about how Informix IDS and SE's optimizers decide what's the best route for processing a query, other than SET EXPLAIN? Is there any documentation which illustrates the ranking of SELECT statements? I would imagine that "SELECT col FROM table WHERE ROWID = n" ranks 1st. What are the rest of them?.. If I'm not mistaking, Informix's ROWID is a SERIAL(INT) which allows for a max. of 2GB nrows, or maybe it uses INT9 for TB's nrows?.. However, I think Oracle uses HEX values for ROWID. Too bad ROWID can't be oftenly used, since a rows ROWID can change. So maybe ROWID is used by the optimizer as a counter? Perhaps, it could be used for implementing the query progress idea I mentioned in my "Begin viewing query results before query completes" question? For some reason, I feel it wouldn't be that difficult to report a query's progress while being processed, perhaps at the expense of some slight overhead, but it would be nice to know ahead of time: A "Google-like" estimate of how many rows meet a query's criteria, display it's progress every 100, 200, 500 or 1,000 rows, give users the ability to cancel it at anytime and start displaying the qualifying rows as they are being put into the current list, while it continues searching?.. This is just one example, perhaps we could think other neat/useful features, the ingridients are more or less there. Perhaps we could fine-tune each query with more granularity than currently available? OLTP queries tend to be mostly static and pre-defined. The "what-if's" are more OLAP, so let's try to add more control and intelligence to it? So, therefore, being able to more precisely control, not "hint-influence" a query is what's needed and therefore it would be necessary to know how the optimizers logic is programmed. We can then have Dynamic SELECT and other statements for specific situations! Maybe even tell IDS to read blocks of indexes nodes at-a-time instead of one-by-one, etc. etc.

  Read the article

• sql server 2005 indexes and low cardinality

  - by Peanut

  How does SQL Server determine whether a table column has low cardinality? The reason I ask is because query optimizer would most probably not use an index on a gender column (values 'm' and 'f'). However how would it determine the cardinality of the gender column to come to that decision? On top of this, if in the unlikely event that I had a million entries in my table and only one entry in the gender column was 'm', would SQL server be able to determine this and use the index to retrieve that single row? Or would it just know there are only 2 distinct values in the column and not use the index? I appreciate the above discusses some poor db design, but I'm just trying to understand how query optimizer comes to its decisions. Many thanks.

  Read the article

• Executing a DTS package from Sql Server 2005

  - by Doerr

  I am trying to run several DTS packages from a sql 2000 box. The DTS calls will originate from .net 2.0 - 3.5 code. I have been unable to find a good way to programmatically accomplish this. What I have read is running a sql job from a stored procedure that calls the DTS package. Does anyone has any experience with this or know of a good way to call the DTS? Note: For the forseeable future these packages will remain DTS. Eventually we will convert them to SSIS. Any insight or experience would be very helpful.

  Read the article

• Execute SQL SP in Excel VBA

  - by TheOCD

  HI I am having problem with getting all the columns back when i execute following code in excel vba. I only get 6 out of 23 columns back. Connection, command etc works fine (i can see exec command in the SQL Profiler), data headers are created for all 23 columns but i only get data for 6 column. Side Note: it's not prod level code, have missed out error handling on purpose, sp works fine in SQL management studio, ASP.Net, C# win form app, it is for Excel 2003 connecting to SQL 2008. Can someone help me troubleshoot it? Dim connection As ADODB.connection Dim recordset As ADODB.recordset Dim command As ADODB.command Dim strProcName As String 'Stored Procedure name Dim strConn As String ' connection string. Dim selectedVal As String 'Set ADODB requirements Set connection = New ADODB.connection Set recordset = New ADODB.recordset Set command = New ADODB.command If Workbooks("Book2.xls").MultiUserEditing = True Then MsgBox "You do not have Exclusive access to the workbook at this time." & _ vbNewLine & "Please have all other users close the workbook and then try again.", vbOKOnly + vbExclamation Exit Sub Else On Error Resume Next ActiveWorkbook.ExclusiveAccess 'On Error GoTo No_Bugs End If 'set the active sheet Set oSht = Workbooks("Book2.xls").Sheets(1) 'get the connection string, if empty just exit strConn = ConnectionString() If strConn = "" Then Exit Sub End If ' selected value, if <NOTHING> just exit selectedVal = selectedValue() If selectedVal = "<NOTHING>" Then Exit Sub End If If Not oSht Is Nothing Then 'Open database connection connection.ConnectionString = strConn connection.Open ' set command stuff. command.ActiveConnection = connection command.CommandText = "GetAlbumByName" command.CommandType = adCmdStoredProc command.Parameters.Refresh command.Parameters(1).Value = selectedVal 'Execute stored procedure and return to a recordset Set recordset = command.Execute() If recordset.BOF = False And recordset.EOF = False Then Sheets("Sheet2").[A1].CopyFromRecordset recordset ' Create headers and copy data With Sheets("Sheet2") For Column = 0 To recordset.Fields.Count - 1 .Cells(1, Column + 1).Value = recordset.Fields(Column).Name Next .Range(.Cells(1, 1), .Cells(1, recordset.Fields.Count)).Font.Bold = True .Cells(2, 1).CopyFromRecordset recordset End With Else MsgBox "b4 BOF or after EOF.", vbOKOnly + vbExclamation End If 'Close database connection and clean up If CBool(recordset.State And adStateOpen) = True Then recordset.Close Set recordset = Nothing If CBool(connection.State And adStateOpen) = True Then connection.Close Set connection = Nothing Else MsgBox "oSheet2 is Nothing.", vbOKOnly + vbExclamation End If

  Read the article

• SQL cast datatime

  - by BahaiResearch.com

  In SQL Server 2005, why does: PRINT Cast('' AS datetime) display: Jan 1 1900 12:00AM I would have thought it should be null?

  Read the article

• SQL joins "going up" two tables

  - by blcArmadillo

  I'm trying to create a moderately complex query with joins: SELECT `history`.`id`, `parts`.`type_id`, `serialized_parts`.`serial`, `history_actions`.`action`, `history`.`date_added` FROM `history_actions`, `history` LEFT OUTER JOIN `parts` ON `parts`.`id` = `history`.`part_id` LEFT OUTER JOIN `serialized_parts` ON `serialized_parts`.`parts_id` = `history`.`part_id` WHERE `history_actions`.`id` = `history`.`action_id` AND `history`.`unit_id` = '1' ORDER BY `history`.`id` DESC I'd like to replace `parts`.`type_id` in the SELECT statement with `part_list`.`name` where the relationship I need to enforce between the two tables is `part_list`.`id` = `parts`.`type_id`. Also I have to use joins because in some cases `history`.`part_id` may be NULL which obviously isn't a valid part id. How would I modify the query to do this?

  Read the article

• What are the best tools for Sql Server version control

  - by Mendy

  After reading this post, and the suggestion to use Team Edition for Database Professionals, I want to know is there any equivalent to this for SQL server 2008 / Visual stuio 2010 ultimate. I'm looking for tool need to do all the thing that Jeff mention in his article: Create test data. Schema comparison. Data comparison. Database unit testing. Refactoring. Integrated T-SQL editor, a first class language construct in the IDE, just like C# and VB.NET.

  Read the article

• Parametrize the WHERE clause?

  - by ControlFlow

  Hi, stackoverflow! I'm need to write an stored procedure for SQL Server 2008 for performing some huge select query and I need filter it results with specifying filtering type via procedure's parameters (parameterize where clause). I found some solutions like this: create table Foo( id bigint, code char, name nvarchar(max)) go insert into Foo values (1,'a','aaa'), (2,'b','bbb'), (3,'c','ccc') go create procedure Bar @FilterType nvarchar(max), @FilterValue nvarchar(max) as begin select * from Foo as f where case @FilterType when 'by_id' then f.id when 'by_code' then f.code when 'by_name' then f.name end = case @FilterType when 'by_id' then cast(@FilterValue as bigint) when 'by_code' then cast(@FilterValue as char) when 'by_name' then @FilterValue end end go exec Bar 'by_id', '1'; exec Bar 'by_code', 'b'; exec Bar 'by_name', 'ccc'; But it doesn't work when the columns has different data types... It's possible to cast all the columns to nvarchar(max) and compare they as strings, but I think it will cause a performance degradation... Is it possible to parameterize where clause in stored procedure without using things like EXEC sp_executesql (dynamic SQL and etc.)?

  Read the article

• SQL joining 3 tables when 1 table is emty

  - by AdRock

  I am trying to write a query that connects 3 tables. The first table is info about each festival The second table is the number of votes for each festival The third table is reviews for each festival I want to join all 3 tables so i get all the feilds from table1, join table1 with table2 on the festivalid but i also need to count the number of records in table 3 that applys to each festival. The first 2 tables give me a result becuase they both have data in them but table 3 is empty becuase there are no reviews yet so adding that to my query fives me no results SELECT f.*, v.total, v.votes, v.festivalid, r.reviewcount as count FROM festivals f INNER JOIN vote v ON f.festivalid = v.festivalid INNER JOIN (SELECT festivalid, count(*) as reviewcount FROM reviews) GROUP BY festivalid) as r on r.festivalid = v.festivalid

  Read the article

• SQL finding members who are not friends of another member

  - by thedirktastik

  I'm struggling to find a query which will return members who aren't friends of a certain member. Here is the layout of my tables: member_login: MemberID, email, password member_info: memberID, first_name, last_name member_friends: friendID, req_member, req_date, app_member, app_date, date_deactivated I tried to use NOT IN to run a query that would return the opposite of friends but nothing I try seems to be working. Here's what I thought would work: SELECT Mi.First_Name, Mi.Last_Name FROM Member_Info Mi WHERE Mi.Memberid NOT IN( SELECT Mi.Memberid, Mi.First_Name, Mi.Last_Name FROM Member_Info Mi, Member_Login Ml, Member_Friends Mf WHERE Mi.Memberid = Ml.Memberid AND (Mi.Memberid = Mf.Req_Member AND Mf.App_Member = 1 OR Mi.Memberid = Mf.App_Member AND Mf.Req_Member =1) AND Ml.Date_Deactivated <= 0 AND Mf.App_Date > 0 ); Any ideas?

  Read the article

• SQL GROUP BY - also SELECT not-constant columns

  - by Michal Kosek

  can someone please help me with this query? I have 2 tables in my database: log_visitors: -------------------- id | host_id log_access: -------------------- visitor | document | timestamp "log_access.visitor" links to "log_visitors.id" Currently, I'm using this query: SELECT log_visitors.host_id , MIM(log_access.timestamp) AS min_timestamp FROM log_access INNER JOIN log_visitors ON (log_access.visitor = log_visitors.id) GROUP BY log_visitors.host_id; to get "MIN(timestamp)" for each "host_id" in the database. HERE'S MY QUESTION: I also need to get "document" for that access with that timestamp... I can't simply add "log_access.document" into SELECT list, since it's not constant and I am not grouping by document... Any ideas?

  Read the article

• Help replace this SQL cursor with better code

  - by user318573

  Can anyone give me a hand improving the performance of this cursor logic from SQL 2000. It runs great in SQl2005 and SQL2008, but takes at least 20 minutes to run in SQL 2000. BTW, I would never choose to use a cursor, and I didn't write this code, just trying to get it to run faster. Upgrading this client to 2005/2008 is not an option in the immediate future. ------------------------------------------------------------------------------- ------- Rollup totals in the chart of accounts hierarchy ------------------------------------------------------------------------------- DECLARE @B_SubTotalAccountID int, @B_Debits money, @B_Credits money, @B_YTDDebits money, @B_YTDCredits money DECLARE Bal CURSOR FAST_FORWARD FOR SELECT SubTotalAccountID, Debits, Credits, YTDDebits, YTDCredits FROM xxx WHERE AccountType = 0 AND SubTotalAccountID Is Not Null and (abs(credits)+abs(debits)+abs(ytdcredits)+abs(ytddebits)<>0) OPEN Bal FETCH NEXT FROM Bal INTO @B_SubTotalAccountID, @B_Debits, @B_Credits, @B_YTDDebits, @B_YTDCredits --For Each Active Account WHILE @@FETCH_STATUS = 0 BEGIN --Loop Until end of subtotal chain is reached WHILE @B_SubTotalAccountID Is Not Null BEGIN UPDATE xxx2 SET Debits = Debits + @B_Debits, Credits = Credits + @B_Credits, YTDDebits = YTDDebits + @B_YTDDebits, YTDCredits = YTDCredits + @B_YTDCredits WHERE GLAccountID = @B_SubTotalAccountID SET @B_SubTotalAccountID = (SELECT SubTotalAccountID FROM xxx2 WHERE GLAccountID = @B_SubTotalAccountID) END FETCH NEXT FROM Bal INTO @B_SubTotalAccountID, @B_Debits, @B_Credits, @B_YTDDebits, @B_YTDCredits END CLOSE Bal DEALLOCATE Bal

  Read the article

• SQL - Finding continuous entries of a given size.

  - by ByteMR

  I am working on a system for reserving seats. A user inputs how many seats they wish to reserve and the database will return a set of suggested seats that are not previously reserved that matches the number of seats being reserved. For instance if I had the table: SeatID | Reserved ----------------- 1 | false 2 | true 3 | false 4 | false 5 | false 6 | true 7 | true 8 | false 9 | false 10 | true And the user inputs that they wish to reserve 2 seats, I would expect the query to return that seats (3, 4), (4, 5), and (8, 9) are not reserved and match the given number of input seats. Seats are organized into sections and rows. Continuous seats must be in the same row. How would I go about structuring this query to work in such a way that it finds all available continuous seats that match the given input?

  Read the article

< Previous Page | 291 292 293 294 295 296 297 298 299 300 301 302  | Next Page >