Why are batch inserts faster? Is it because the connection and setup overhead for inserting a single row is the same for a set of rows? What other factors make batch inserts faster? How do batch updates work? Assuming the table has no uniqueness constraints, insert statements don't really have any effect on other insert statements in th...
I have a stored procedure that accepts a date input that is later set to the current date if no value is passed in: CREATE PROCEDURE MyProc @MyDate DATETIME = NULL AS IF @MyDate IS NULL SET @MyDate = CURRENT_TIMESTAMP -- Do Something using @MyDate I'm having problems whereby if @MyDate is passed in as NULL when the stored ...
From my initial readings on unit testing (I'm a beginner) it is wise to put all of your setups and tests in a separate project from the code being tested. This seems ideal to me, as well. However, I've recently begun reading The Art of Unit Testing, trying to discover how to break dependencies on things such as database calls. The met...
Hi all, I'm attempting to write a stored proc that takes in a number, n, and returns the first n results for a given query, exclusively locking those n rows. I'm a little new to SQL and I'm having a bit of difficulty matching data types correctly. My package spec looks like this: PACKAGE package IS Type out_result_type is REF CURS...
I am writing a class that will (hopefully) allow manipulation of data through a LINQ to SQL layer without having to know what individual objects you are working with. So far, it works fine for cascading selects and inserts, but I am having a hard time with updates. Here is the save method I am using: if ((long)entity.GetType().GetProp...
I am trying to filter an SQL database. Each row represents a user, the main column I’m focusing on is titled last_visited and formatted as… 2009-06-17 12:15:32. How many users have visited in the last day/week/month? Like in: SELECT COUNT(*) AS USERS_TODAY FROM parts_users Where updated_at > (NOW()-7) ...
Can anyone reccomend a free .NET libary which allows you to expose a SQL Query builder to your users in a windows form app? I'd like my users to be able to run relatively straight forward SELECT statements, including some JOINS and other multi-table operations without getting into the real nitty-gritty of SQL. Thanks, sweeney ...
I'm getting this strange error while processing a large number of data... Error Number: 1267 Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=' SELECT COUNT(*) as num from keywords WHERE campaignId='12' AND LCASE(keyword)='hello again 昔 ã‹ã‚‰ ã‚ã‚‹ å ´æ‰€' What can I do to re...
Input: Name Id N1 1 N1 3 N1 4 N1 7 N2 2 N2 1 N2 8 N2 5 N3 4 N3 8 N3 5 N3 3 N4 7 N4 7 N4 7 N4 8 Output: Name1 Name2 Name3 Name4 ---------------------...
Let's say I have a function call on a select or where clause in Oracle like this: select a, b, c, dbms_crypto.hash(utl_raw.cast_to_raw('HELLO'),3) from my_table A similar example can be constructed for MS SQLServer. What's the expected behavior in each case? Is the HASH function going to be called once for each row in the table, o...
My Rails app is starting to need complicated queries. Should I just start using raw SQL queries? What is the trend in the Rails community? Update: I do not have written queries right now, I wanted to ask this question before I start. But here is an example of what I want to do: I have books which have categories. I want to say- ...
I need to pull a specific substring from a string of the form: foo=abc;bar=def;baz=ghi For example, how would I get the value of "bar" from that string? ...
I have always wondered how Facebook designed the friend <-> user relation. I figure the user table is something like this: user_email PK user_id PK password I figure the table with user's data (sex, age etc connected via user email I would assume). How does it connect all the friends to this user? Something like this? user_id...
I have 3 models: Books, Notifications, and NotificationTypes. Books have notifications as the Notification model has a book_id. Notifications have one notification_type as the Notification model has one notification_type_id I want all Books created between date1 and date2 books_set1 = Book.find :all, :conditions => ["created_at <= ? A...
Lets setup the question first. What I have is >4 tables: Customer, Address, Order, OrderItems. Each are mapped using Hibernate Annotations and accessed through a Spring DAO/Services layer. What I am trying to do is merge duplicate customers together. So all that really needs to happen is all orders and addresses associated with custom...
In another question I posted someone told me that there is a difference between: @variable and: variable in MySQL. He also mentioned how MSSQL has batch scope and MySQL has session scope. Can someone elaborate on this for me? ...
If my table has a huge number of columns (over 80) should I split it into several tables with a 1-to-1 relationship or just keep it as it is? Why? My main concern is performance. PS - my table is already in 3rd normal form. PS2 - I am using MS Sql Server 2008. PS3 - I do not need to access all table data at once, but rather have 3 dif...
I'm running this code: RESTORE DATABASE [MyDB_2009May11] FROM DISK = N'C:\DB Copy\MyDB_2009May11.bak' WITH MOVE N'Archive_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\MyDB_2009May11.mdf', MOVE N'Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\MyDB_2009May11.mdf'...
Hi, I am getting errors while trying to insert an email address in a record. I think they are related to the @ symbol. Is there something special I need to do with the @ symbol? ...
Here is my situation. I have the following tables: Product Product Attribute Order Product (references a product_id and an order_id) Order Product Attribute (references an order_product and a product_attribute) When an admin goes in to edit a product attribute (for instance "color"), he may delete that attribute by mistake and later ...