Hot answers tagged

54

Your inner loop appears to be unnecessary. Why not create a lookup: var knownAccountNumbers = new HashSet<string>( dataStructure.Tables["AccountData"].Rows .Cast<DataRow>() .Select(row => row[0].ToString())); Now your loop is simply: foreach (DataRow row in dt.Rows) { var accountNumber = row[0].ToString().Replace("\""...


14

The code appears to be broken, you're not break;ing after finding a match so all records will likely have row[12] == "N". You should really be doing a join on accountNumber: var matchingRows = from DataRow row in dt.Rows let rowKey = row[0].ToString().Replace("\"", "") join DataRow queryRow in dataStructure.Tables["AccountData"].Rows on ...


10

Performance It should be possible to accomplish this task in seconds rather than minutes, with the right data structure. This is your main mistake: paid = list(set(t)) The problem is, for a list with n items, it takes O(n) time to check whether the list contains a particular item. It's particularly bad if the vast majority of the entries that you are ...


9

If I'm understanding it correctly, you're populating a ConcurrentDictionary from the values of two other ConcurrentDictionaries, where the keys are equal. Try this, it's vastly faster than your loop in my tests. var matches = FirstDictionary.Keys.Intersect(SecondDictionary.Keys); foreach (var m in matches) ResultDictionary.TryAdd(FirstDictionary[m], ...


8

A few comments: Avoid memory intensive data types Instead of using a DataTable for dt, just read from the csv directly, one line at a time (readLine() and then split(','). This will greatly cut down on your memory usage, rather than loading all 1.7 million rows at once, when you only use one at a time. Sorted data is faster Sort dataStructure by account ...


8

Put simply you change an \$O(1)\$ operation to a \$O(n)\$ one. Dictionary's have \$O(1)\$ key lookup and so if you change your inner loop to, SecondDictionary.TryGetValue. It'll achieve the same results without going through every item in SecondDictionary.


7

I think your problem is do with the amount of cartesian products you are forming. I would approach something like this, as I alluded to in chat: with salesYTD as ( SELECT SalesPerson, InvoiceDate, Sum(Amount) As AmountOnDay FROM sales WHERE InvoiceDate > DATEADD(year, DATEDIFF(year, 0, GETDATE()), 0) GROUP By SalesPerson, InvoiceDate ), ...


7

I honestly don't see anything particularly wrong with that Linq query, but there are a number of other things you should consider: Converting the query to a List<T> forces immediate evaluation of the query and requires the allocation of an single, contiguous portion of memory to store the results. If there are several thousand records, this can be ...


7

You should be using the clause group by SELECT h.id, h.name, im.id, im.name -- What you want to select FROM house h,image im -- Tables in join WHERE h.id = im.house_id -- The join (equivalent to inner join) GROUP BY h.id -- This compresses all entries with the ...


6

Style Guides The Perl community does not have a single universal style – there is more than one way to do it. But sometimes, consistency isn't a bad thing either. Here are the three important cornerstones of Perl style: A perlstyle manpage exists which explains a sensible core of a style guide. Damian Conway published the Perl Best Practices book with many ...


6

If after adding the index on latitude and longitude you still see poor performance, trim the query down to the bare minimum to isolate the source. Remove all of the joins and the where clause referencing those tables. Once it's doing just the distance calculation (which should be move to a deterministic stored function), you can get a better time measurement....


6

I don't know if there is a faster way, but I would use sub-queries. For example: select top 20 h.id, h.name, im.mid, i.name from _house h join ( select min(id) as mid,house_id from _image group by house_id ) im on im.house_id=h.id join _image i on i.id=im.mid Depending on the context it might be faster to generate a temporary table with just one image per ...


6

General remarks The first line should be #!/bin/bash. Don't use `cmd` style command substitution, it's deprecated. Use $(cmd) instead, it's better. Tests like [ "$var" = "$other" ] are also deprecated. Use [[ $var = $other ]] instead. As you can see in this example, you can omit the double-quotes in this modern version. The http://www.shellcheck.net/ ...


6

DataTables are quite powefull and offer lots of the real database functionality. Also as far as joins are concerned a few things are possible and I'm of the opinion that if someone uses DataTables he also should use the functionality they offer ;-) It this case using DataTable joins your example could look like this: DataSet ds = new DataSet(); DataTable ...


6

Your code does loop 700 000 multiplied by 65 000 multiplied by the number of elements in each cluster. That is a lot of iterations, and not very useful. The better approach would be to read the smaller file into memory, and then read the larger file line by line. In addition as you iterate over each row in the smaller file, matching each of the keys, it ...


6

You could use a Dictionary and make properties of UserViewModel to address elements of this dictionary as follows: public class UserViewModel { private readonly Dictionary<SocialType, string> SocialLinks; public UserViewModel(User user) { SocialLinks = (user.SocialLinks ?? new SocialLink[0]) .ToDictionary(x =&...


6

I am understanding that your goal is to return a set of data that a user is authorized to see. I further understand that ChannelUsers and WorkGroupUsers are only used to validate the authorization. (I see you do select ChannelUsers.user_id but I don't think you really need it - you can take it from Users.id instead, I believe). Given that, I believe you ...


5

There is a flaw in your logic for the first week of the year. If a sale happens for a repcode/category in the last week of the previous year, but a sale for that same repcode/category has not yet happened in the current year, then the LW value will not be represented in the final result because it will not successfully join in the left outer join. You can ...


5

You're doing four joins which are identical except for the date range. That suggests that you ought to be working with the date ranges as a kind of data, rather than as code or as tables or as columns. Let's make that happen using some temporary tables: create table #dateRanges ( Period NVARCHAR(3) , StartDateIncl DATE , EndDateExcl DATE ); insert into #...


5

You use the value of (@bucketSize + @bufferSize) inside of a where clause and to calculate a value in a select statement, I think if you took that and created it's own variable, that it might give you some performance increase. as well as some of the other arithmetic that could be done from the start and not calculated during the actual query len(@values)/(...


5

The complexity here is much more than \$O(\log{n})\$. Let's go though it... There's one \$O(o)\$ loop, where \$o\$ is the size of the outer List. Now, for each outer loop, we then loop through all the members of innerList. If that has size \$i\$, the combined complexity is now \$O(o \times i)\$. Inside that inner loop you then remove members of the ...


5

Right now you aren't using generics: Map hashMap; // no generics :o Map<String, Object> hashMap; // generics :D The variable result can only be null in the function innerJoinHash(): result.add(new Result(sportsMan.getName(), sportsMan.getRank(), sportsMan.getSport(), sportsMan.getRank())); This is because you are initializing result to null: List&...


5

I see no index on the latitude and longitude columns of the location table. Try CREATE INDEX location_latlng ON `location` (`latitude`, `longitude`); If it's still slow, then add the output of EXPLAIN SELECT to your question.


5

Your query looks OK, for the most part. There are two things you could do better. C and O don't make for very good aliases. Picture if you had 20 tables instead of just 2. In programming it's better to give aliases, variables, etc. meaningful names. Aliases are useful for shortening long table names, but should not be short to the point it obfuscates your ...


5

Here are a couple of points about your standard subs & functions: I've mentioned in another post that IsWorkbookOpen has a side-affect of activating the workbook. At first glance the FindStringInRange doesn't seem to do anything, it's a sub, but then you realise that it changes the ByRef rngFoundCell Range variable. I think this should be a function ...


5

Based on @Johnbot comment Right know you are iterating through all the records of dataStructure.Tables["AccountData"] regardless if you have found a match. You really should break out of this loop if acctNum.Equals(queryAcctNum). This should speed up your task a lot (at least if the data could be found). Another possible enhancement could be to sort ...


5

I think this looks very good, but I would make a few small changes: Instead of getting separate columns for month and day, use one column with date_trunc('day', subscriptions.created_at). Then you still have a timestamp that you can format, sort by, etc. Also you will avoid problems when someone reports on a range crossing New Years. Be aware that binning ...


5

Performance Your problem report baffles me. You say that you can successfully produce inputv.dat, a 2.6 GB file, in 45 minutes. Yet, filtering inputv.dat to produce output_veloc_max.dat fails after 30 hours? The only possible bottleneck is if num in linenum — and that would only be inefficient if the number of nodes is huge. To make it work efficiently ...


5

Your query could be simplified. use inner join to avoid ambiguity when reading the query temporal interval join date between start and end (inclusive end) join business_calendar as bc on (bc.date_begin < d.dl_valutation_date or bc.date_begin = d.dl_valutation_date) and (dateadd(day, 6, bc.date_begin) > d.dl_valutation_date ...


4

Assuming that each post has exactly one submitter, this is a more natural formulation of the query. SELECT posts.* , users.username , users.firstname , users.lastname , users.avatar FROM posts JOIN users ON posts.submitter = users.username WHERE posts.id < $lastpost AND posts.position = ...


Only top voted, non community-wiki answers of a minimum length are eligible