Search Results

Search found 31931 results on 1278 pages for 'sql statement'.

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

Page 647/1278 | < Previous Page | 643 644 645 646 647 648 649 650 651 652 653 654  | Next Page >

  • MySQL - are FK's useful / viable in a web app?

    - by yoda
    Hi all, I've encountered this discussion related to FK's and web applications. Basically some people say that FK's in web applications doesn't represent a real improvement and can even make the application slower in some cases. What do you guys think, what's your experience? -- A quote from Heikki Tuuri, creator of InnoDB engine, founder and CEO of Innobase: InnoDB checks foreign keys as soon as a row is updated, no batching is performed or checks delayed till transaction commit Foreign keys are often serious performance overhead, but help maintain data consistency Foreign Keys increase amount of row level locking done and can make it spread to a lot of tables besides the ones directly updated

    Read the article

  • How to model parent to child pair in MySQL (SQL)

    - by mikeschuld
    I have a data model that includes element types Stage, Actor, and Form. Logically, Stages can be assigned pairs of ( Form <--- Actor ) which can be duplicated many times (i.e. same person and same form added to the same stage at a later date/time). Right now I am modeling this with these tables: Stage Form Actor Form_Actor _______________ |Id | |FormId | --> Id in Form |ActorId | --> Id in Actor Stage_FormActor __________________ |Id | |StageId | --> Id in Stage |FormActorId | --> Id in Form_Actor I am using CodeSmith to generate the data layer for this setup and none of the templates really know how to handle this type of relationship correctly when generating classes. Ideally, the ORM would have Stage.FormActors where FormActor would be the pair Form, Actor. Is this the correct way to model these relationships. I have tried using all three Ids in one table as well Stage_Form_Actor ______________ |Id | |StageId | --> Id in Stage |FormId | --> Id in Form |ActorId | --> Id in Actor This doesn't really get generated very well either. Ideas?

    Read the article

  • GROUP BY as a way to pick the first row from a group of similar rows, is this correct, is there any

    - by FipS
    I have a table which stores test results like this: user | score | time -----+-------+------ aaa | 90% | 10:30 bbb | 50% | 9:15 *** aaa | 85% | 10:15 aaa | 90% | 11:00 *** ... What I need is to get the top 10 users: user | score | time -----+-------+------ aaa | 90% | 11:00 bbb | 50% | 9:15 ... I've come up with the following SELECT: SELECT * FROM (SELECT user, score, time FROM tests_score ORDER BY user, score DESC, time DESC) t1 GROUP BY user ORDER BY score DESC, time LIMIT 10 It works fine but I'm not quite sure if my use of ORDER BY is the right way to pick the first row of each group of sorted records. Is there any better practice to achieve the same result? (I use MySQL 5)

    Read the article

  • Speeding up inner-joins and subqueries while restricting row size and table membership

    - by hiffy
    I'm developing an rss feed reader that uses a bayesian filter to filter out boring blog posts. The Stream table is meant to act as a FIFO buffer from which the webapp will consume 'entries'. I use it to store the temporary relationship between entries, users and bayesian filter classifications. After a user marks an entry as read, it will be added to the metadata table (so that a user isn't presented with material they have already read), and deleted from the stream table. Every three minutes, a background process will repopulate the Stream table with new entries (i.e. whenever the daemon adds new entries after the checks the rss feeds for updates). Problem: The query I came up with is hella slow. More importantly, the Stream table only needs to hold one hundred unread entries at a time; it'll reduce duplication, make processing faster and give me some flexibility with how I display the entries. The query (takes about 9 seconds on 3600 items with no indexes): insert into stream(entry_id, user_id) select entries.id, subscriptions_users.user_id from entries inner join subscriptions_users on subscriptions_users.subscription_id = entries.subscription_id where subscriptions_users.user_id = 1 and entries.id not in (select entry_id from metadata where metadata.user_id = 1) and entries.id not in (select entry_id from stream where user_id = 1); The query explained: insert into stream all of the entries from a user's subscription list (subscriptions_users) that the user has not read (i.e. do not exist in metadata) and which do not already exist in the stream. Attempted solution: adding limit 100 to the end speeds up the query considerably, but upon repeated executions will keep on adding a different set of 100 entries that do not already exist in the table (with each successful query taking longer and longer). This is close but not quite what I wanted to do. Does anyone have any advice (nosql?) or know a more efficient way of composing the query?

    Read the article

  • (N)Hibernate: deleting orphaned ternary association rows when either associated row is deleted.

    - by anthony
    I have a ternary association table created using the following mapping: <map name="Associations" table="FooToBar"> <key column="Foo_id"/> <index-many-to-many class="Bar" column="Bar_id"/> <element column="AssociationValue" /> </map> I have 3 tables, Foo, Bar, and FooToBar. When I delete a row from the Foo table, the associated row (or rows) in FooToBar is automatically deleted. This is good. When I delete a row from the Bar table, the associated row (or rows) in FooToBar remain, with a stale reference to a Bar id that no longer exists. This is bad. How can I modify my hbm.xml to remove stale FooToBar rows when deleting from the Bar table?

    Read the article

  • MYSQL Convert rows to columns performance problem

    - by Tarski
    I am doing a query that converts rows to columns similar to this post but have encountered a performance problem. Here is the query:- SELECT Info.Customer, Answers.Answer, Answers.AnswerDescription, Details.Code1, Details.Code2, Details.Code3 FROM Info LEFT OUTER JOIN Answers ON Info.AnswerID = Answers.AnswerID LEFT OUTER JOIN (SELECT ReferenceNo, MAX(CASE DetailsIndicator WHEN 'cde1' THEN DetailsCode ELSE NULL END ) Code1, MAX(CASE DetailsIndicator WHEN 'cde2' THEN DetailsCode ELSE NULL END ) Code2, MAX(CASE DetailsIndicator WHEN 'cde3' THEN DetailsCode ELSE NULL END ) Code3 FROM DetailsData GROUP BY ReferenceNo) Details ON Info.ReferenceNo = Details.ReferenceNo There are less than 300 rows returned, but the Details table is about 180 thousand rows. The query takes 45 seconds to run and needs to take only a few seconds. When I type show processlist; into MYSQL it is hanging on "Sending Data". Any thoughts as to what the performance problem might be?

    Read the article

  • Increase increment size to match GUID advantage

    - by TenaciousImpy
    Hi, I've been thinking of implementing this system, but can't help but feel there's a catch somewhere. One of the points of using GUID over incrementing int is that, in the future, if you were to merge databases together, you wouldn't have any clashes over the primary key/identifier. However, my approach is to set the increment size to X where X is the number of servers I'll most likely have in the future. Then, on each server, have the seed be an increment over the seed number on the previous server. That way, during merging, there would be no clashes with the primary key. Is this a safe, normal method or have I gone mental :)? Thanks

    Read the article

  • Howto monitor traffic between IIS and MSSQL

    - by kockiren
    Hello @all, i try to check how much traffic flows between MSSQL Server and IIS Server in different Locations. There are 1 ipcop in every Location and i download the tcpdump file from one Firewall and search for DST=ipmssql and SRC=ipIIS but i did not find the ip from the Database Server. But there are traffic between both. Any suggestions why i did not find the IP Adress from the MSSQL Server? Is this an configuration failure in IPCop or is the Traffic between ISS and MSSQL so strange :-) Regards Rene

    Read the article

  • Data Access Layer in an ASP.NET website

    - by user3519124
    :) i have a DAL class file in my project, that my teacher sent me and explained to me but i did not really understand it. It has number of functions, and I understand only few of them, like with connecting to the database or creating a command object but there are 2 that I dont understand: public static DataTable GetTable(string str) { OleDbConnection con = DAL.GetConnection(); OleDbCommand cmd = DAL.GetCommand(con, str); DataTable dt = new DataTable(); OleDbDataAdapter adp = new OleDbDataAdapter(); adp.SelectCommand = cmd; adp.Fill(dt); return dt; } public static int ExecuteNonQuery(string str) { int num = -1; OleDbConnection con = DAL.GetConnection(); con.Open(); if (con.State == ConnectionState.Open) { OleDbCommand cmd = DAL.GetCommand(con, str); num = cmd.ExecuteNonQuery(); con.Close(); } return num; } thank you :)

    Read the article

  • Why am I getting "Enter Parameter Value" when running my MS Access query?

    - by DanM
    In my query, I use the IIF function to assign either "Before" or "After" to a field named BeforeOrAfter using AS. When I run this query, however, the "Enter Parameter Value" dialog appears, requesting a value for BeforeOrAfter. If I remove BeforeOrAfter DESC from the ORDER BY clause, I don't get the dialog. Here is the offending query: SELECT d.Scenario, e.Event, IIF(d.LogTime < e.Time, 'Before','After') AS BeforeOrAfter, d.HeartRate FROM Data d INNER JOIN Events e ON d.Scenario = e.Scenario WHERE e.Include = Yes ORDER BY d.Scenario, e.Id, BeforeOrAfter DESC Question: Why is my AS BeforeOrAfter not being recognized by the ORDER BY clause? Why does it ask me to enter a parameter value for "BeforeOrAfter" when I run this query? Note: I tried using brackets, single quotes, double quotes, etc., but none of that made any difference.

    Read the article

  • Compare and find differences in two tables in Oracle

    - by Ruslan
    Hi! i have 2 tables: account: ID, ACC, AE_CCY, DRCR_IND, AMOUNT, MODULE flex: ID, ACC, AE_CCY, DRCR_IND, AMOUNT, MODULE I want to show differences comparing only by: AE_CCY, DRCR_IND, AMOUNT, MODULE and ACC by first 4 characters Example: ID ACC AE_CCY DRCR_IND AMOUNT MODULE -- --------- ------ -------- ------ ------ 1 734647674 USD D 100 OP and in flex: ID ACC AE_CCY DRCR_IND AMOUNT MODULE -- --------- ------ -------- ------ ------ 1 734647654 USD D 100 OP 2 734665474 USD D 100 OP 9 734611111 USD D 100 OP ID's 2 and 9 should be shown as differences. If I use FULL JOIN I'll get no differences as substr(account.ACC,1,4) = substr(flex.ACC,1,4) are equal and others are equal and MINUS doesn't work because ID's different. Thanks.

    Read the article

  • Repository vs Data Access

    - by vdh_ant
    Hi guys In the context of the n-tier application, is there a difference between what you would consider your data access classes to be and your repositories? I tend to think yes but I just wanted to see what other thought. My thinking is that the job of the repository is just to contain and execute the raw query itself, where as the data access class would create the context, execute the repository (passing in the context), handle mapping the data model to the domain model and return the result back up... What do you guys think? Also do you see any of this changing in a Linq to XML scenario (assuming that you change the context for the relevant XDocument)? Cheers Anthony

    Read the article

  • query optimization

    - by Gaurav
    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 table structure of the friend table is uid1(int), uid2(int). This table is used to determine whether two users are linked together as friends. Any workaround? Can I write the query in a different way? Or is there some other way to solve this issue. I'm sure I am not the first person to face such a problem. Any help would be greatly appreciated.

    Read the article

  • help with exception handling in linq

    - by stackoverflowuser
    I have the following code to retrieve customer name, total (orders ), sum (order details) for reach customer in Northwind database. The problem with below code is that it raises an exception since a few customers dont have any entry in orders table. I know using the query syntax (join) the exception can be avoided. I want to know if the same can be handled with the extension method syntax. CustomerOrderDataContext db = new CustomerOrderDataContext(); var customerOrders = db.Customers.Select(c => new { CompanyName = c.CompanyName, TotalOrders = c.Orders.Count(), TotalQuantity = c.Orders.SelectMany(o => o.Order_Details).Sum(o=>o.Quantity) });

    Read the article

  • How do you compare dates in a LINQ query?

    - by Gina
    I am tring to compare a date from a asp calendar control to a date in the table.... here's what i have... it doesn't like the == ? var query = from details in db.TD_TravelCalendar_Details where details.StartDate == calStartDate.SelectedDate && details.EndDate == calEndDate.SelectedDate select details;

    Read the article

  • How do these user/userParam references relate to the Customer and Account lookups?

    - by plath
    In the following code example how do the user/userParam references relate to the Customer and Account lookups and what is the relationship between Customer and Account? // PersistenceManager pm = ...; Transaction tx = pm.currentTransaction(); User user = userService.currentUser(); List<Account> accounts = new ArrayList<Account>(); try { tx.begin(); Query query = pm.newQuery("select from Customer " + "where user == userParam " + "parameters User userParam"); List<Customer> customers = (List<Customer>) query.execute(user); query = pm.newQuery("select from Account " + "where parent-pk == keyParam " + "parameters Key keyParam"); for (Customer customer : customers) { accounts.addAll((List<Account>) query.execute(customer.key)); } } finally { if (tx.isActive()) { tx.rollback(); } }

    Read the article

  • counting rows that date hasn't yet passed

    - by AdRock
    I am trying to count the number of rows whose date has not yet passed so i can get only the current records I get an error sayng MySQL error #111 Invalid use of group function SELECT COUNT(festivalid) FROM festivals WHERE min(datefrom) > now()

    Read the article

  • MySQL database query returns empty result

    - by user1791096
    I am doing a data migration and getting empty result of simple query with one join. Following is the query Select * from users u INNER JOIN temp_users tu ON tu.uid = u.uid There hundreds of records which have same uid in both tables, but this query returns only one record. Following is the structure of tables users table uid: varchar(50) utf8_general_ci Yes NULL temp_users table uid: varchar(50) utf8_general_ci Yes NULL Is there anyone who faced same problem?

    Read the article

  • What is the best way to return result from business layer to presentation layer when using linq - I

    - by samsur
    I have a business layer that has DTOs that are used in the presentation layer. This application uses entity framework. Here is an example of a class called RoleDTO public class RoleDTO { public Guid RoleId { get; set; } public string RoleName { get; set; } public string RoleDescription { get; set; } public int? OrganizationId { get; set; } } In the BLL I want to have a method that returns a list of DTO.. I would like to know which is the better approach: returning IQueryable or list of DTOs. Although i feel that returning Iqueryable is not a good idea because the connection needs to be open. Here are the 2 different methods using the different approaches public class RoleBLL { private servicedeskEntities sde; public RoleBLL() { sde = new servicedeskEntities(); } public IQueryable<RoleDTO> GetAllRoles() { IQueryable<RoleDTO> role = from r in sde.Roles select new RoleDTO() { RoleId = r.RoleID, RoleName = r.RoleName, RoleDescription = r.RoleDescription, OrganizationId = r.OrganizationId }; return role; } Note: in the above method the datacontext is a private attribute and set in the constructor, so that the connection stays opened. Second approach public static List GetAllRoles() { List roleDTO = new List(); using (servicedeskEntities sde = new servicedeskEntities()) { var roles = from pri in sde.Roles select new { pri.RoleID, pri.RoleName, pri.RoleDescription }; //Add the role entites to the DTO list and return. This is necessary as anonymous types can be returned acrosss methods foreach (var item in roles) { RoleDTO roleItem = new RoleDTO(); roleItem.RoleId = item.RoleID; roleItem.RoleDescription = item.RoleDescription; roleItem.RoleName = item.RoleName; roleDTO.Add(roleItem); } return roleDTO; } Please let me know, if there is a better approach - Thanks,

    Read the article

  • Updating Excel Cell with Non-Numeric Data in C#

    - by kbo206
    I have a query that is ExcelQuery = "Update [Sheet1$] set CITIZEN_ID = #" + value + " where CITIZEN_ID = " + value; As you can see, I'm essentially just appending a "#" onto the CITIZEN_ID field. value is a int/numeric value. So if I had "256" in the CITIZEN_ID column it would be converted to "#256" When I execute this I get an OleDbException Syntax error in date in query expression so I surrounded part of the query in single quotes like this, ExcelQuery = "Update [Sheet1$] set CITIZEN_ID = '#" + value + "' where CITIZEN_ID = " + value; With that I get yet another OleDbException this time with, Data type mismatch in criteria expression. I'm guessing for some reason the CITIZEN_ID fields don't want to take anything besides a plain number. Is there any way I can remedy this to get that pound symbol in? Thanks!

    Read the article

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

< Previous Page | 643 644 645 646 647 648 649 650 651 652 653 654  | Next Page >